SQL Server 2005 Security : Authentication and Authorization

2/27/2013 8:29:10 PM

Before we dive into the concepts of authentication and authorization, it is important to discuss a new feature in SQL Server 2005 called Endpoints. In previous versions of SQL Server, clients could connect via TCP, Named Pipes, Shared Memory, and VIA. As long as one of these protocols was enabled on the server and the user had a valid login, the connection was accepted. SQL Server 2005 introduces a separation of this behavior via Endpoints.

Endpoints can be considered a point of entry into SQL Server. Administrators can create an endpoint for not only TCP, Named Pipes, Shared Memory, and VIA, but also for HTTP. Once an endpoint is created, you can restrict access so users can connect only via a certain endpoint type. For example, you might create a login called Login1 and grant access to the HTTP endpoint and deny access to all other endpoints (TCP, Named Pipes, Shared Memory, and VIA). In this state, Login1 can access SQL Server only via the HTTP endpoint; it cannot directly connect to SQL Server via TCP or any other protocol. To see how this endpoint verification affects authentication, let’s consider the process of making a client connection.

How Clients Establish a Connection

If a TCP client wants to make a connection to SQL Server, it must first know which port to connect to. In previous versions of SQL Server, there was always a thread waiting on UDP port 1434. Its purpose was to give back details on all of the instances of SQL Server that were running, as well as the port numbers of those instances. All a client had to do was make a connection to this port and determine which port it wanted to connect to, given a specific instance of SQL Server. This process generally worked until hackers found a way to launch a Denial of Service (DoS) attack on SQL Server by continuously sending packets to this port requesting the enumeration. Because this process of enumerating was part of the SQL Server service, the “SQL Slammer” worm virus created some headaches for SQL Server users. In SQL Server 2005, this functionality has been pulled out into a separate service called the SQL Server Browser service. Now you can turn this functionality on and off without touching the SQL Server service itself.


You can mitigate the DoS issue with the browsing service if you block the 1434 port on your firewall. However, if you are concerned about intranet attacks, you might want to consider not running this service and instead explicitly passing the port numbers on the connections strings.

After the network connection request and pre-login handshake has been made, SQL Server must authenticate the user to make sure he has access to begin with. Figure 1 depicts the authentication process.

Figure 1. SQL Server 2005 authentication model

At this point, the service accepts the login credentials supplied by the user and attempts to validate them. If successful, the login is authorized against the endpoint corresponding to the type of connection made to SQL Server. In this example, the login is checked to see if it has been granted CONNECT permissions to the TCP endpoint. If this is true, the authentication process proceeds; otherwise, the connection fails at this point.


By default, new logins are automatically granted CONNECT permissions to the TCP endpoint.

Once the login has passed the endpoint check, SQL Server switches to a database context (the default database specified for the login or specified in the connection string). It then attempts to authenticate the login as a user of that database. If this is successful, the connection succeeds; otherwise, it fails. Now that a database context is established and the login has been authenticated, the user can perform his work against the server.

Password Policies

In the Windows world, administrators can set login expirations and enforce password policies (for example, requiring passwords to be a certain length and contain a mixture of special characters). Traditionally in SQL Server, SQL logins never respected these global policy settings. In SQL Server 2005, both Windows-authenticated and SQL logins obey the group policy settings defined in the Windows domain.


Password policy enforcement is available only in SQL Server running on Windows Server 2003 and later.

To help illustrate password policies, let’s define a minimum password length using the Local Security Settings applet located in the Administrative Tools folder of the Control Panel. This tool is shown in Figure 2.

Figure 2. Password Policy node of Local Security Settings

Using this tool, we can change a variety of parameters. For this example, we will change the minimum password length to eight characters. Once this change has been made, we can proceed with creating our new login.

Instead of using sp_addlogin, as previous versions of SQL Server did, SQL Server 2005 has a new DDL for creating logins: CREATE LOGIN.


When this command is executed, it will fail because our password has only three characters and we specified a minimum of eight.

Msg 15116, Level 16, State 1, Line 1
Password validation failed. The password does not meet
         policy requirements because it is too short.

The new system view sys.sql_logins returns information about the login such as whether the policy and expiration are set, but if you want additional information regarding the login, such as how many bad passwords for this login were submitted, you can use the LOGINPROPERTY built-in function, as shown here.

DECLARE @name varchar(20)

SET @name='foo'

SELECT LOGINPROPERTY( @name, 'PasswordLastSetTime' ) AS PasswordLastSetTime,
LOGINPROPERTY( @name, 'IsExpired' ) AS IsExpiried,
LOGINPROPERTY( @name, 'IsLocked' ) AS IsLocked,
LOGINPROPERTY( @name, 'IsMustChange' ) AS IsMustChange,
LOGINPROPERTY( @name, 'LockoutTime' ) AS LockoutTime,
LOGINPROPERTY( @name, 'BadPasswordCount' ) AS BadPasswordCount,
LOGINPROPERTY( @name, 'BadPasswordTime' ) AS BadPasswordTime,
LOGINPROPERTY( @name, 'HistoryLength' ) AS HistoryLength,
LOGINPROPERTY( @name, 'PasswordHash' ) AS PasswordHash


User-Schema Separation

Imagine an employee in an organization who is a heavy SQL Server user. She has created many tables and stored procedures, and then suddenly one day she is let go by the company. As a database administrator, your task is to reassign ownership of all the objects that she created and owned. In previous versions of SQL Server, this was a tedious task. In SQL Server 2005, the task is made a lot easier because of the schema feature.

SQL Server 2005 introduces a different concept of schemas than in previous versions of SQL Server. In SQL Server 2000, database users and schemas are equivalent. Every database user is the owner of a schema that has the same name as the user. An owner of an object is effectively identical to the owner of the schema that contains the object. This one-to-one mapping behavior is what makes ownership reassignment so tedious.

SQL Server 2005 separates users and schemas, and schemas are independent buckets that can contain zero or more objects. Users can own one or more schemas and can be assigned a default schema. If a default schema is not specified, the user is defaulted to the dbo schema of the database. This default schema is used to resolve the names of securables that are referred to without using their fully qualified name. In SQL Server 2000, the location that is checked first is the schema owned by the calling database user, followed by the schema owned by dbo. In SQL Server 2005, each user has a default schema, so when SQL Server resolves the names of securables, it checks the default schema.

You can thus transfer ownership of many securables, and a schema can contain securables owned by a database user other than the owner of the schema. This is important to note because if you leverage the old system views, such as sysobjects, the results returned in queries will not show up correctly. You must use a catalog view, such sys.objects, instead.

The CREATE SCHEMA DDL is used to create a new schema. It can also be used to create tables and views within the new schema, and to set GRANT, DENY, or REVOKE permissions on those objects, as shown in the following example:

CREATE TABLE Leads (id int, name varchar(max),phone nvarchar(20))
GRANT SELECT ON Sales.Leads TO Tammie
DENY SELECT ON Sales.Leads TO Vince;

This example creates the Sales schema and assigns it to Rob. A Leads table is created within the Sales schema, and Tammie is granted select access to the table while Vince is denied select access.

For Tammie to access the table in SQL 2000, she must use Rob for the schema name, as shown here:

SELECT * FROM Production.SalesReps.Rob.Leads

In SQL Server 2005, she can use the Sales schema name instead:

SELECT * FROM Production.SalesReps.Sales.Leads

If Rob ever quits or gets fired, all the sysadmin has to do to transfer ownership of the Sales schema to Tammie is the following:


The abstraction of database users from schemas provides many benefits to developers and administrators, including the following:

  • Multiple users can own a single schema through membership in roles or Windows groups. This extends the familiar functionality and allows roles and groups to own objects.

  • Dropping database users is greatly simplified.

  • Dropping a database user does not require renaming objects that are contained by the schema of that user. Therefore, you do not have to revise and test applications that refer explicitly to schema-contained securables after dropping the user who created them.

  • Multiple users can share one default schema for uniform name resolution.

  • Shared default schemas allow developers to store shared objects in a schema that was created specifically for a specific application, instead of in the dbo schema.

  • Permissions on schemas and schema-contained securables can be managed with a higher degree of detail than in earlier releases.

  • Fully qualified object names have four parts: server.database.schema.object.

Execution Context

Granting and managing permissions to non-sysadmin users has always been an interesting challenge, especially when it comes to users who own stored procedures that use tables and other objects they do not own. In a typical example, suppose there are three users: User1, User2, and User3. User3 owns a table, CustomerInformation. User2 has written a stored procedure that enumerates only the customer name from this table. User1 needs to execute User2’s stored procedure. With previous versions of SQL Server, User1 not only needs EXECUTE permission on User2’s stored procedure, but she also needs access to the underlying CustomerInformation table owned by User3. Now multiply this requirement across an enterprise, and you can see the permission management nightmare that unfolds. With SQL Server 2005, User2 can change the execution context of the stored procedure to any of the following:

  • Execute as Caller Executes under the credentials of the caller. This is the same default behavior as in previous versions of SQL Server.

  • Execute as Self Executes under the credentials of the user who last modified the stored procedure.

  • Execute as <insert name of login> Executes under the credentials of the login specified. For this to work, the user creating or modifying the stored procedure must have IMPERSONATE permission for the login specified.


    EXECUTE AS also takes a database user name. If you use a login, the scope for the impersonation is server level. If you use a database user, the scope is for the database only.

  • Execute as Owner Executes under the credentials of the login who owns the stored procedure. If the owner changes after the object was created, the execution context is automatically mapped to the new owner.

Referring back to our example, let us write our original problem (Listing 1).

Listing 1. Execution context example
--Create our users
create login User1 with password='^*ahfn2@^(K'
create login User2 with password='*HABa7s7aas'
create login User3 with password='zxd837&^gqF'
create database ExampleDB
use ExampleDB
--User 3 will own a table
create user User3 with default_schema=User3
create schema User3 authorization User3
--User 2 will have SELECT access and write a proc to access
create user User2 with default_schema=User2
create schema User2 authorization User2

--User 1 will have the right to exec the proc
create user User1 with default_schema=User1
create schema User1 authorization User1

grant create table to User3
grant create proc to User2
execute as login='User3'
create table User3.CustomerInformation
(CustomerName nvarchar(50))
insert into CustomerInformation values ('Bryan''s Bowling Alley')
insert into CustomerInformation values ('Tammie''s Tavern')
insert into CustomerInformation values ('Frank''s Fresh Produce')
grant select on CustomerInformation to User2
execute as login='User2'
--create a stored proc that will return the rows in our table
create proc ViewCustomerNames
select * from User3.CustomerInformation
grant execute on ViewCustomerNames to User1


Now that all of the users and objects are created, let’s log in as User1 and try to directly access the table and stored procedure.

execute as login='User1'
--Notice User1 cannot access table directly
select * from User3.CustomerInformation
--User1 can execute the procedure but does not have permissions on the underlying table
exec User2.ViewCustomerNames


At this point, in previous versions of SQL Server we would have to give User1 some type of access against User3’s table. In SQL Server 2005, User2 can change the execution context of the stored procedure. In our example, User2 will change the execution context to EXECUTE AS OWNER. This will cause the stored procedure to be executed as User2 because he is the owner of the stored procedure.

execute as login='User2'
select * from User3.CustomerInformation

Now when User1 executes User2’s stored procedure, the stored procedure will be executing under the credentials of User2 and thus have access to User3’s table only within the context of the stored procedure. This allows User1 to execute User2’s stored procedure without having explicit access to the underlying table.

execute as login='User1'
--User1 still cannot access table directly
select * from User3.CustomerInformation
--User1 can execute a procedure that uses the CustomerInformation table
exec User2.ViewCustomerNames

Another form of execution context switching is used within the previous example. In SQL Server 2005 it is possible to change the execution context of the current connection without having to close and reopen the connection. The user must either be a sysadmin or have IMPERSONATE permission on the login for this to work. Alternatively, you can use this same statement to switch context for a database user. When used for switching context on a database, the scope of impersonation is restricted to the current database. In a context switch to a database user, the server-level permissions of that user are not inherited. In addition, a user must either be a sysadmin or have IMPERSONATE permission on the user of the database.

Executing context switching is a powerful and efficient way to reduce the number of permissions to manage. For developers and sysadmins, it provides an easy way to test scripts and debug without having to log out and reconnect to SQL Server.

Top 10
Review : Sigma 24mm f/1.4 DG HSM Art
Review : Canon EF11-24mm f/4L USM
Review : Creative Sound Blaster Roar 2
Review : Philips Fidelio M2L
Review : Alienware 17 - Dell's Alienware laptops
Review Smartwatch : Wellograph
Review : Xiaomi Redmi 2
Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8