Labour Day Special : Flat $299 off on live classes + 2 free self-paced courses! - SCHEDULE CALL
Ans:- When a TCP client aims to connect to a SQL Server, it must determine the appropriate port to use. Before SQL Server 2005, a thread was continuously waiting on User Datagram Protocol (UDP) port 1434 to provide information about all running SQL Server instances and their corresponding port numbers. A client could establish a UDP connection to port 1434 and specify the desired port to connect to, based on a specific instance of SQL Server. This approach was effective until hackers exploited it, launching Denial of Service (DoS) attacks on SQL Servers by incessantly sending packets to port 1434 for enumeration. The "SQL Slammer" worm virus caused significant issues due to this vulnerability. Since SQL Server 2005, this functionality has been segregated into a separate service called the SQL Server Browser service, allowing enabling or disabling without affecting the SQL Server service itself.
As a result, the Browser service is now susceptible to DoS attacks, and a precautionary measure is to block port 1434 on the firewall. If concerned about intranet attacks within the firewall, one should consider disabling the Browser service and explicitly specifying port numbers in the connection strings.
Image: SQL Server Authentication Model
At this stage, the service verifies the user's login credentials and attempts to validate them. A successful login is authorized against the endpoint associated with the type of connection made to the SQL Server. In this case, the login's CONNECT permissions to the TCP endpoint are checked. If successful, the authentication process proceeds; otherwise, the connection fails at this point.
New logins are automatically granted CONNECT permissions to the TCP endpoint by default. SQL Server switches to a database context (default database specified for the login or in the connection string) once the login passes the endpoint check. It then tries to authenticate the login as a database user. The connection succeeds if the login can be authenticated; otherwise, it fails. Once a database context is established, and login is authenticated, the user can interact with the database on the server. Further information can be found in Online SQL certification courses.
Ans:- In Windows, administrators can set login expirations and enforce password policies, such as requiring passwords to meet specific criteria (length, special characters, etc.). SQL logins in SQL Server have not previously adhered to these global policy settings. However, since SQL Server 2005, both Windows-authenticated and SQL logins have been subject to the Windows domain's group policy settings. It's essential to note that password policy enforcement is available only in SQL Server versions 2003 and later.
Image: Password Policy node of Local Security Settings
This tool allows you to modify several parameters. For instance, you can set a minimum password length to eight characters. If you attempt to create a login that doesn't meet the Windows password policy:
CREATE LOGIN SomeUser WITH PASSWORD = '2short', CHECK POLICY-ON, CHECK EXPIRATION-ON
Attempting to create a login that does not satisfy the Windows password policy.This statement is incorrect as the password is only seven characters long, whereas the Windows password policy requires a minimum of eight characters.
The system view sys.sql logins provides information about logins, including policy and expiration settings. For more specific information about a particular login, such as the number of bad passwords attempted, you can use the LOGINPROPERTY built-in function.
SELECT 'IsLocked' AS Property, UNION ALL SELECT 'IsExpired', UNION ALL SELECT 'IsMustChange', UNION ALL SELECT 'BadPasswordCount', UNION ALL SELECT 'PasswordLastSetTime", UNION ALL SELECT 'BadPasswordTime', UNION ALL SELECT 'LockoutTime', LOGINPROPERTY('sa', 'IsLocked') AS Value LOGINPROPERTY('sa', 'IsExpired') LOGINPROPERTY('sa', 'IsMustChange') LOGINPROPERTY('sa', 'BadPasswordCount') LOGINPROPERTY('sa', 'PasswordLastSetTime") LOGINPROPERTY('sa', 'BadPasswordTime') LOGINPROPERTY('sa', 'LockoutTime')
Querying the LOGINPROPERTY function.
The query results display various properties of the same login.
To gain a deeper understanding of this topic, you can explore Online SQL certification courses.
Ans:- SQL Server 2005 introduced a novel schema concept distinct from previous versions. In SQL Server 2000 and earlier, database users and schemas were synonymous. Each database user was the owner of a schema with the same name. This one-to-one mapping made reassigning ownership cumbersome.
However, starting with SQL Server 2005, users and schemas became separate entities. Schemas became independent containers capable of housing zero or more objects. Users could own multiple schemas, and a default schema was assigned to them. If no default schema was specified, the user defaulted to the database's "dbo" schema. This default schema facilitated name resolution for security variables referenced without their fully qualified name. In SQL Server 2000, the schema owned by the calling database user was checked first, followed by the "dbo" schema.
This separation offers several benefits:
Ans:- To create a new schema, the CREATE SCHEMA statement is utilized. It can also be employed to create tables and views within the new schema and to grant, deny, or revoke access to those objects.
USE master GO - Create three server logins CREATE LOGIN Rob WITH PASSWORD 'jackpot_0' CREATE LOGIN Tammie WITH PASSWORD= 'jackpot_0' CREATE LOGIN Vince WITH PASSWORD= 'jackpot_0' -- Create a new database IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB') DROP DATABASE MyDB GO CREATE DATABASE MYDB GO USE MyDB GO -- Create three database users mapped to the logins CREATE USER Rob FOR LOGIN Rob CREATE USER Tammie FOR LOGIN Tammie CREATE USER Vince FOR LOGIN Vince GO Create a schema owned by Rob CREATE SCHEMA Sales AUTHORIZATION Rob GO -- Create a table in the schema owned by Rob CREATE TABLE Sales. Leads (id int, name varchar(50), phone varchar(20)) -- Allow Tammie but not Vince to SELECT from the table in the schema owned by Rob GRANT SELECT ON Sales. Leads TO Tammie DENY SELECT ON Sales. Leads TO Vince
Granting and denying permissions to a schema table
This example assigns the Sales schema to Rob. Within the Sales schema, a Leads table is created, granting Tammie select access to the table while denying Vince the same access. Before SQL Server 2005, Tammie would have needed to use Rob as the schema name, but now, with SQL Server 2005, she can use the Sales schema name directly:
SELECT * FROM Production.SalesReps.Sales.Leads
In the event that Rob no longer holds the Sales schema, the sysadmin user can transfer ownership to Tammie with a simple statement:
ALTER AUTHORIZATION ON SCHEMA::Sales TO Tammie
If you find these code segments intriguing, consider exploring Online SQL certification programs to delve deeper into this subject.
Ans:- The separation of database users and schemas brings forth numerous advantages for both developers and administrators. These advantages include:
Ans:- The task of granting and managing permissions for non-sysadmin users has always been a captivating challenge, especially when users own stored procedures that interact with tables and objects beyond their ownership. Which involves three logins: Login1, Login2, and Login3. These logins correspond to database users User1, User2, and User3, each possessing their respective schemas: Schema1, Schema2, and Schema3.
USE master GO -- Create 3 logins CREATE LOGIN Login1 WITH PASSWORD = 'P@$$word1' CREATE LOGIN Login2 WITH PASSWORD = 'P@$$word2' CREATE LOGIN Login3 WITH PASSWORD GO 'P@$$word3' - Create a new database IF EXISTS(SELECT name FROM sys.databases WHERE name = "MyD8") DROP DATABASE MyDB CO CREATE DATABASE MYDB 00 USE MYOB GO -- Create 3 users mapped to the 3 logins CREATE USER User3 FOR LOGIN Login) CREATE USER User2 FOR LOGIN Login2 CREATE USER User1 FOR LOGIN Loginl -- Create a corresponding schema for each of the 3 users CREATE SCHEMA Schema3 AUTHORIZATION User3 00 CREATE SCHEMA Schema2 AUTHORIZATION User2 GO CREATE SCHEMA Schemal AUTHORIZATION User1 00 -- Let User3 create tables and let User2 create stored procedures GRANT CREATE TABLE TO User3 GRANT CREATE PROCEDURE TO User2 00 -- Impersonate Login3 (User3) EXECUTE AS LOGIN = 'Login' -- Create and populate a table in Schema3 CREATE TABLE Schema3.Region (RegionName nvarchar(50)) INSERT INTO Schema3.Region VALUES("East Coast'), ('West Coast'), ('Midwest') -- Allow User2 to SELECT from the Schema3 table GRANT SELECT ON Schema3.Region TO User2 00 REVERT 00 --Impersonate Login (User2) EXECUTE AS LOGIN = 'Login' 00 -- Create a stored procedure in Schema2 that selects from the table in Schema3 CREATE PROCEDURE Schema2.GetRegions AS SELECT * FROM Schema3.Region --Allow User1 to EXECUTE the Schema2 stored procedure GRANT EXECUTE ON Schema2.GetRegions TO User1 00 REVERT GO -- User1 cannot access the Schema3 table, even via a Schema2 stored --proc that they have permission to execute EXECUTE AS LOGIN = 'Login' GO SELECT * FROM Schema3.Region -- fails EXEC Schema2.GetRegions GO REVERT GO -- fails -- Modify the Schema2 stored proc so that it always run in the context -- of the owner, not the caller EXECUTE AS LOGIN = 'Login2' GO ALTER PROCEDURE Schema2.GetRegions WITH EXECUTE AS OWNER AS SELECT FROM Schema3. Region GO REVERT GO -- User1 still cannot access the Schema3 table directly, but now they can -- indirectly via the Schema2 stored procedure EXECUTE AS LOGIN = 'Login' GO SELECT * FROM Schema3. Region EXEC Schema2.GetRegions GO REVERT fails -- works! GO
In this scenario, User3 owns the Schema3.Region table, while User2 is the proprietor of the Schema2.GetRegions stored procedure, responsible for retrieving data from said table. This arrangement functions seamlessly due to User2's SELECT permission granted on User3's table.
However, a challenge arises when User1 must execute User2's stored procedure. While User1 possesses EXECUTE permission on the stored procedure, the execution will fail due to the absence of SELECT permission on the underlying table accessed by the procedure. Prior to SQL Server 2005, a simple resolution to this problem was elusive. When this requirement is multiplied across an enterprise, the complexities of permissions management become apparent.
Thankfully, starting from SQL Server 2005, addressing this issue has become straightforward. Switching the execution context (a specialized form of impersonation) between various logins and database users can now be effortlessly achieved. For instance, in our example, User2 can adjust the execution context of the Schema2.GetRegions stored procedure by using one of the various WITH EXECUTE AS clauses available in an ALTER PROCEDURE statement.
These WITH EXECUTE AS options include:
Previously mentioned, User1 encounters difficulties accessing the Schema3 table owned by User3 without altering the execution context. Any attempt will prompt SQL Server to return an error message stating: "The SELECT permission was denied on the object 'Region', database 'MyDB', schema 'Schema3'."
Prior to SQL Server 2005, granting User1 access to User3's table was a prerequisite for running User2's stored procedure. However, starting with SQL Server 2005, User2 can conveniently modify the execution context of the stored procedure, eliminating the necessity for User1 to be granted direct access to User3's table. By changing the execution context in Listing 5-4 to EXECUTE AS OWNER, User2 ensures the stored procedure is executed with User2's credentials, given that User2 is the stored procedure's owner.
As User1 runs User2's stored procedure, it operates under User2's credentials, granting access to User3's table. This access remains confined to the context of the stored procedure. As a result, User1 can execute User2's stored procedure without needing direct access to the underlying table owned by User3.
We've uncovered the remarkable benefits brought by schema separation and execution context in SQL Server. The separation of database users and schemas has revolutionized the way developers and administrators manage their systems. Through shared ownership, streamlined user removal, and precise permissions management, this approach brings unprecedented flexibility and security.Furthermore, we've seen how execution context addresses the complex challenge of granting permissions to non-sysadmin users, especially those owning stored procedures that access external objects. With SQL Server 2005's powerful features, such as the WITH EXECUTE AS clauses, users can seamlessly adjust the execution context, ensuring necessary permissions without compromising security.
By understanding these concepts and harnessing their capabilities, you'll be better equipped to optimize your SQL Server environment. Whether you're a developer seeking more robust functionality or an administrator aiming to simplify user management, schema separation and execution context are essential tools in your toolkit.Stay updated on the latest advancements in SQL Server and database management by exploring online SQL certification programs. Continuous learning will empower you to master these techniques and keep your databases secure, efficient, and future-ready.
SQL Server MERGE Statement: Question and Answer
Mastering INSERT and OVER DML Syntax: Interview Questions Guide
SQL CLR Deployment and Error Resolution: Question and Answer
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment