The bank needs to make sure you are who you claim you are. They do this by asking for identification, traditionally a driver's license. This action is called authentication. SQL Server makes sure you are who you claim you are by asking for credentials. You give SQL Server credentials in one of two ways. You can give SQL Server a username and password combination. SQL Server will take a hash of the password you supplied and compare it with a hash of the password that is stored in its internal tables. If the two hashes match for the given username, you are authenticated. This type of authentication is known as SQL Server authentication.

The other way to authenticate to SQL Server is through Windows authentication. With this type, you do not need to type in a password; rather, since you are logged into Windows, SQL Server asks Windows to verify your identity via your Windows security token.

In an ideal world, you would use only Windows authentication, but the world is hardly ever ideal. The main advantage with a pure Windows authentication environment is a streamlined administration experience—you don't have to manage yet another set of credentials. To tell SQL Server what authentication modes to support, there is a server property called Server Authentication. It has one of two possible settings: Windows Authentication and SQL Server and Windows Authentication mode (which is also referred to as Mixed Mode). When SQL Server is in Windows Authentication mode, users will not be able to log in using SQL Server authentication. If Mixed Mode is selected, SQL Server will accept both Windows-authenticated and SQL Server-authenticated logins. Since it is easier to show examples using SQL Server-authenticated accounts vs. having to create a Windows user, this book's examples will use SQL Server in SQL Server and Windows Authentication mode. To see which authentication mode your server is using, launch the Server Properties dialog box in SSMS by selecting Properties from the context menu of a SQL Server instance. Click the Security page within the Server Properties dialog box to see the authentication mode setting, as shown in Figure 14-1.

Figure 14.1. Portion of Server Properties dialog box showing the Security page

Getting back to the safety deposit box example, now that the bank has validated your identity using your driver's license, they give you the box. The box is locked with a key. Possessing this key authorizes you to open the box. In SQL Server, just because you have authenticated yourself and have a SQL Server login account, you do not necessarily have access to items contained within the server. The DBA has to explicitly authorize a user's access to objects within SQL Server. The DBA can grant access to one or more objects to a group of users or to a single user. You will learn more about granting permissions later in this chapter.

Databases within SQL Server are their own unique entities. Databases are designed to be easily detached from one server environment and reattached to another server without any extra work being done by a DBA. This concept is slightly different from what other database vendors implement, which is typically to marry the database and server instance more closely. With this independence come some additional security concepts to understand.

Databases have their own users called database users and their own roles called database roles. You will learn more about database roles later in this chapter. To authorize a SQL Server login access to a particular database, DBAs need to create a database user within the requested database, which maps to a SQL Server login. Database users are not shared between any databases on the server instance, but a single SQL Server login can map to one or more database users with each database user being in a different database. In Figure 14-2, there is a single SQL Server login called Howard_Login. This login is mapped to two different database users. In the Sales database, Howard_Login is mapped to Howard_Sales_User. In the HR database, Howard_Login is mapped to Howard_HR_User.

Figure 14.2. Representation of server login and database users

Since databases are their own unique entities, the permissions defined for Howard_Sales_User and Howard_HR_User can be different. In Figure 14-2's scenario, Howard logs in to SQL Server just once. He then has access to both the Sales and HR databases, with whatever permissions he has been given on each of those.

To create a new SQL Server login from SQL Server Management Studio (SSMS), navigate down the Object Explorer tree to the Logins node, which is a child of the Security node. Right-click, and select the New Login context menu item. This will launch the Login – New dialog box, as shown in Figure 14-3.

Figure 14.3. Login – New dialog box

The Login – New dialog box allows you to create four different types of logins. They are as follows: Windows authentication, SQL Server authentication, mapped to certificate, and mapped to asymmetric key. If you wanted to create a SQL Server login mapped to a Windows account, you select the "Windows authentication" radio button and type the Windows user account in the text box provided. This option does not require you to provide any password for the user. If you wanted to create a SQL Server– authenticated login, simply supply a login name and type a password. When you select the SQL Server Authentication radio button, you will notice three additional check boxes become available. "Enforce password policy" tells SQL Server to honor any Active Directory group policy configuration for passwords. "Enforce password expiration" tells SQL Server to remind users to change old passwords and to lock out expired logins. "Users must change password at next login" is self-explanatory. SSMS has a nice dialog box that pops up for the user if this option is selected and it's the first time the user has logged into SQL Server.

SQL Server provides the ability to securely store credentials. These credentials are used for SQL Server Agent proxy accounts or for use with a cryptographic provider. The Map to Credential check box allows the DBA to add credentials that will be available to a given login for use with these specific features. If you are still wondering why you would ever want to store credentials to be used by a SQL Server login, consider this scenario. When you connect to SQL Server using SQL Server authentication, you have no identity to the Windows OS. If you want this SQL Server login to do something on the server that requires a Windows identity, using a credential solves the problem

The dialog box in Figure 14-3 contains four other pages that have additional options. Using these other pages, you can add the login you are creating to server roles, map the login to database users, and configure other settings.

If you click OK on the General page, the login will be created. The T-SQL statement that creates a login is CREATE LOGIN. An example of creating the TestLogin login is as follows:

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'PaSsWoRd!'
 MUST_CHANGE, DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

At the server instance level, there are nine fixed server roles that you can assign to a SQL Server login. Fixed means you as a DBA cannot create your own server roles; rather, you have only the nine to choose from. Assigning a principal to a server role allows that principal certain privileges within the SQL Server instance. Table 14-1 describes the nine fixed server roles.

Before you start assigning logins to various roles, it is important to know that in reality you probably will use very few of these roles. The most popular is the sysdamin role. The functionality of these roles has been superceded with the introduction of server permissions. Server roles are still relevant to learn and use because in some cases it is not possible to create a SQL Server login with enough server permissions to mimic a server role.

To add a SQL Server login to a server role, you can use the Server Roles tab in the Login – New dialog box (shown earlier in Figure 14-3). To add roles to a SQL Server login using T-SQL, you can use the sp_addsrvrolemember system stored procedure. For example, to give the dbcreator role to the TestLogin login, you can use the following script:

USE [master]
GO
EXEC sys.sp_addsrvrolemember @loginame = N'TestLogin', @rolename = N'sysadmin'
GO

Before server permissions, if you wanted a login to be able to shut down the server, you needed to assign that login to the serveradmin role. Yet serveradmin imparts a number of other capabilities that you may not want to give that other user, such as the ability to change server-wide settings. With server permissions, you can now grant just the SHUTDOWN permission to a specific login, as shown in the following code:

USE master
GO
GRANT SHUTDOWN TO <login>
GO

where <login> is the name of the SQL Server login that you want to grant the SHUTDOWN permission to. There are 30 permissions that can be granted at the server instance scope. Table 14-2 shows a complete list of these permissions.

Each one of these permissions applies to one or more securables. A securable is a resource to which SQL Server regulates access. In Table 14-2, you can see that some securables are servers, some are logins, and some are endpoints. By using these permissions, you be very granular in what you allow other users to do.

An endpoint is a point of entry into SQL Server. Prior to SQL Server 2005, if you had a valid SQL Server login, you could connect via TCP/IP, Shared Memory, or any other protocol that was enabled on the server. With endpoints, you can now restrict what kinds of protocols a specific SQL Server login can connect by. Restricting available protocols reduces the area of attack for hackers. If you allow your sysadmin accounts to connect via Shared Memory only, you won't have to worry that someone remote will try to connect as sysadmin over a network connection. Shared Memory clients can connect to only those server instances that are on the local server, so you have to be physically sitting at the machine or within a remote desktop session. As a best practice (and if possible to do within your organization), consider locking down access to sysadmin accounts.

In addition to the transport protocol, endpoints specify the payload. Payloads define the type of traffic that is allowed. For example, if you can create an HTTP endpoint that listens to SOAP traffic and assign the CONNECT permission to a specific SQL Server login, that SQL Server login can then submit SOAP queries via HTTP to SQL Server. Endpoints are highly leveraged by two key components within SQL Server: Service Broker and database mirroring. When you set up these features within SQL Server, you may notice additional endpoints created.

The sys.server_endpoints catalog view displays both system and user endpoints. Since there are no user-defined endpoints on the queried server, Table 14-3 simply shows the system endpoints that are available out of the box.

SELECT Name,Protocol_desc from sys.endpoints

The previous SELECT statement yields the information represented in Table 14-3.

By default, all SQL Server logins are granted CONNECT permissions to all the system endpoints listed in Table 14-3 with the exception of the Dedicated Admin Connection (DAC). The DAC is a special endpoint available only to members of the sysadmin role. It's a special single-user connection that is designed for an administrator to connect if they cannot connect via their normal connection method to SQL Server. The DAC is useful if a particular process ID within SQL Server is consuming too many resources, which in turn prevents additional user connections. With DAC, an administrator can always connect and troubleshoot any issues.

To help illustrate the power of endpoints, let's create a login, MyAppLogin, and allow it to connect only via Shared Memory. You do this by denying access to all the other protocols.

CREATE LOGIN MyAppLogin WITH PASSWORD='PaSsWoRd1'
GO
DENY CONNECT on ENDPOINT::[TSQL Default TCP] to MyAppLogin
GO
DENY CONNECT on ENDPOINT::[TSQL Default VIA] to MyAppLogin
GO
DENY CONNECT on ENDPOINT::[TSQL Named Pipes] to MyAppLogin
GO

You can test the ability to connect to SQL Server as MyAppLogin through SQL Server Management Studio. When you make a connection in SSMS, the connection dialog box has an Options button. Clicking that will allow you to select the Connection Properties tab. On this tab is a Network Protocol drop-down box. Here, you can force a certain protocol to be used when SSMS makes the connection to SQL. If you run the previous script and attempt to connect using TCP/IP with valid credentials, your connection request will fail. However, if you specify Shared Memory, the connection will succeed. This is illustrating the fact that you denied access to these endpoints for the MyAppLogin account.

Managing SQL Server logins and their memberships to each server role can be done using SQL Server Management Studio. There are also a number of system catalog views that help DBAs manage this information. For a complete list, read the "Security Catalog Views" article in SQL Server Books Online.

When you create a database, there are a few database users created for you. One of them is dbo. Dbo is called the database owner; as the name implies, it has permission to perform all activities within the database. Any member of the sysadmin fixed server role who accesses a database is automatically mapped to the dbo user.

Guest is another database user that is always created. When a SQL Server login accesses a database where they do not have a mapping to a database user, they are automatically mapped to the guest account. Guest is created and is disabled by default. Thus, the default behavior for SQL Server logins that have no specific mapping is to not have any access at all to the database.

sys and INFORMATION_SCHEMA are created and used by SQL Server to provide you with views, dynamical management views, and catalog views of information about your database. An example of a catalog view is sys.database_princpals. This catalog view will show you information about database users and roles for the given database.

When a database user is created, the user is assigned to a default schema. A schema is a logical collection of database objects. By grouping objects in a schema, a DBA can grant permissions to the schema that in turn would affect all the objects within the schema.

USE master
GO
CREATE DATABASE Accounting

GO
CREATE LOGIN DevLogin WITH PASSWORD='asdif983*#@YRfjndsgfD'
GO
USE Accounting
GO
CREATE USER DevUser FOR LOGIN DevLogin
GO
GRANT CREATE TABLE TO DevUser
GO

USE Accounting
GO
CREATE TABLE Customers
(id INT NOT NULL,
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(40) NOT NULL)
GO

Msg 2760, Level 16, State 1, Line 1
The specified schema name "dbo" either does not exist or you do
 not have permission to use it.

GRANT ALTER ON SCHEMA::dbo TO DevUser

CREATE SCHEMA People
GO
GRANT ALTER ON SCHEMA::People TO DevUser
GO

USE Accounting
GO
CREATE TABLE [People.Customers]
(id INT NOT NULL,
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(40) NOT NULL)
GO

CREATE TABLE [SQLPRODUCTION_2.Accounting.People.Customers]

CREATE USER DevUser FOR LOGIN DevLogin
WITH DEFAULT_SCHEMA = People

ALTER AUTHORIZATION ON SCHEMA::People TO TestUser

There are two kinds of database roles: fixed and flexible. Fixed roles cannot be deleted and are provided within every database. Table 14-4 lists fixed database roles.

In addition to using the UI in SSMS, numerous stored procedures and functions help DBAs work with database roles. The article "Database-Level Roles" in SQL Server Books Online does a good job at enumerating this list.

These fixed roles serve to define a general permission within the database. For example, the following script grants DevUser the db_datareader permission using the sp_addrolemember system stored procedure:

USE [Accounting]
GO
EXEC sp_addrolemember N'db_datareader', N'DevUser'
GO

In this example, DevUser would be able to read any table within the database regardless of whether they had SELECT permission defined.

The global behavior of fixed database roles addresses some scenarios; however, to reduce the surface area for attack, it's better for you to grant specific permissions on objects to your users.

GRANT SELECT ON Customers TO BusinessAnalysts

REVOKE SELECT ON Customers TO BusinessAnalysts

DENY SELECT ON Customers to Bob

If your database consisted of just a couple of users, then it would be very easy for you to manage permission for these users directly. However, in the real world, DBAs manage lots of users and, more commonly, many different types of users. A developer will have different requirements than a business analyst. If your organization has 35 developers and 70 business analysts, that is a lot of permissions to manage. To alleviate this burden, you can create a database role, add database users or other roles to this new role, and assign permissions to the role. Now, any time you have a new developer, all you have to do is add their username to the role, and they have all the necessary permissions.

To create a database role, use the CREATE ROLE statement, as shown here:

USE AdventureWorks
GO
CREATE ROLE Developers AUTHORIZATION DevManager
GO

Here you are creating a new role, Developers, and making the DevManager user the owner of this new role. As an owner, you can freely add and remove membership to the role.

To add users to the role, use the sp_addrolemember stored procedure as follows:

sp_addrolemember 'Developers', 'Bryan'

This assumes that there is a database user within the database named Bryan.

Once you defined a role, you can grant permission to the role using the GRANT statement as follows:

GRANT CREATE TABLE TO Developers

GRANT CREATE TABLE TO DevManager WITH GRANT OPTION

Now that you know how to grant, revoke, and deny permissions, I'll introduce a series of functions that are designed to help you in managing security. Although most of this information is available in SQL Server Management Studio dialog boxes, the following functions and catalog views are useful if you prefer to issue Transact-SQL statements instead of using the UI.

SELECT * FROM fn_my_permissions(NULL, 'DATABASE'),

SELECT HAS_PERMS_BY_NAME('Customers', 'OBJECT', 'SELECT')

EXECUTE AS USER='Bryan'
GO
SELECT HAS_PERMS_BY_NAME('Customers', 'OBJECT', 'SELECT')
GO