SQL Server provides a number of settings for controlling access to data, metadata, and other SQL Server resources. For those who are new to SQL Server security or don\u2019t manage security routinely, configuring these settings can seem a complex and confusing process. If you get the settings right, everybody is happy, but if you get them wrong, your users might not be able to access the data they need or, worse still, unauthorized users might be able to get at sensitive information or carry out malicious attacks.<\/p>\n
To effectively protect SQL Server, you must be able to provide approved users with the access they need to specific SQL Server resources, without compromising those or other resources, a process that involves the use of three important types of components:<\/p>\n
Together, these three types of components can help you protect a SQL Server environment and its data at every level, from the availability groups down to the individual database objects. Generally, the process used for providing access to SQL Server resources is to create the principals you need and then configure them with the necessary permissions to access specific securables.<\/p>\n
Logins provide a mechanism for users to connect to the SQL Server platform at the server level. A login serves as a user\u2019s initial entry point into the system. Generally, your first step in providing access for your users is to set up the necessary logins so they can make the initial connections to the server. The database engine supports two types of logins: Windows and SQL Server.<\/p>\n
A Windows login can be based on a Windows domain account\u2014either an individual user account or a group account\u2014or on a Windows local account. If based on a group account, any permissions granted to that login apply to all members of the group, which can be a handy approach to configuring security for multiple users who require the same level of access.<\/p>\n
To create a login, you can use SQL Server Management Studio (SSMS) or use the CREATE<\/strong> LOGIN<\/strong> T-SQL statement. For example, the following statement creates a login based for a local Windows user account (WinUser1<\/strong>) on the srv01b<\/strong> Windows computer:<\/p>\n The FROM<\/strong> WINDOWS<\/strong> clause indicates that the login should be mapped to a Windows account. In this case, we\u2019ve specified the computer name and account name for a local user, but we could have instead specified a domain user, following the same format, as in WinDomain1\\WinUsr1<\/strong>. The CREATE<\/strong> LOGIN<\/strong> statement supports more options of course, depending on the type of user account being specified, but overall the statement is fairly straightforward. You\u2019re merely creating an object that exists at the server level to provide access to the SQL Server environment.<\/p>\n If you\u2019re SQL Server installation is configured to run in mixed mode\u2014that is, it supports both Windows and SQL Server authentication methods\u2014you can also create SQL Server logins, which are specific to a SQL Server instance and not tied to Windows accounts in any way. Although Windows logins are generally the recommended approach, you might need to support SQL Server logins in specific situations, such as providing access to users who are not tied to a Windows domain. The built-in sa<\/strong> account, the bane of many in IT, is a type of SQL Server login.<\/p>\n When users try to connect to SQL Server, the database engine validates their logins against the master<\/strong> database. This allows them to connect to the SQL Server instance, although they won\u2019t have the permissions necessary to do much else. In fact, to be able to access database resources, the login must be tied to a specific database user principal, which is then granted the permissions necessary to access the database objects.<\/p>\n A login\u2019s scope applies to the database engine as a whole, but it does not provide access to the individual database components. For the users associated with those logins to be able to access database resources, you must create users within the target databases and map those principals back to the server logins. You can create a database user with the same name as its associated login or with a different name. You can also map a login to users in multiple databases; however, a login can be mapped to only one user within a database at any given time.<\/p>\n As with server-level logins, you can use SSMS or T-SQL to create a database user, but you must do so within the context of the specific database. For example, we can create a database user named DbUser1<\/strong> within the AdventureWorks2014<\/strong> database, based on the srv01b\\WinUser1<\/strong> login<\/p>\n The FOR<\/strong> LOGIN<\/strong> clause provides the necessary mapping back to the server login. We could have created the user with the same name as the login, but I used a different name here to demonstrate how easy it is to choose whatever name you like, as long as it is unique within the database. This approach also allows us to simplify the qualified name being used for the login.<\/p>\n As you can see, creating a database user that maps back to a server login is a fairly straightforward process. The two principals work in conjunction with each other to enable a user to access the server and database, assuming the necessary permissions have also been configured for that user.<\/p>\n Note that you can also create database users that do not map back to a server login. In other words, you can create users without creating logins. These types of users are generally used for contained databases that can be easily moved between SQL Server instances. For this reason, a database\u2019s containment feature must be enabled before you can create these types of users.<\/p>\n After you create a login or user principal, you can configure permissions on either one to control access at the server or database level. Before you do that, however, consider that SQL Server also supports a third type of principal, referred to as a role<\/em>. A role can exist at either the server or database level and can help manage user access to securables more efficiently.<\/p>\n You can think of a role as a type of container for holding one or more logins, users, or other roles, similar to how a Windows group can hold multiple individual and group accounts. This can make managing multiple principals easier when those principals require the same type of access to SQL Server. You can configure each role with permissions to specific resources, adding or removing logins and users from these roles as needed.<\/p>\n SQL Server supports three types of roles: server, database, and application. Server roles share the same scope as logins, which means they operate at the server level and pertain to the database engine as a whole. As a result, you can add only server-level principals to the roles, and you can configure the roles with permissions only to server-level securables, not database-level securables.<\/p>\n SQL Server provides a set of fixed server roles that are each preconfigured with a set of permissions that cannot be changed. The fixed roles allow the principals assigned to those roles to carry out specific tasks. For example, members of the sysadmin<\/strong> fixed server role can perform any actions on the server, but members of the serveradmin<\/strong> role can only change server-wide configuration options and shut down the server.<\/p>\n To view a list of fixed server roles, you can run the sp_helpsrvrole<\/strong> system stored procedure, as shown in the following example:<\/p>\n As of SQL Server 2012, you can also create user-defined server roles that let you configure the permissions however you need to. Creating a server role is as simple as creating any other type of principle. For instance, you can define a CREATE<\/strong> SERVER<\/strong> ROLE<\/strong> statement that specifies nothing more than the name of the role, as shown in the following example:<\/p>\n In this case, we\u2019re creating a server role name SrvRole1<\/strong>. However, the role exists only as a server-level object on the SQL Server instance, without the capacity to do anything. From here, we must add the login principals we want to include in the role and eventually configure the role with the necessary permissions. (We can actually add the principals and configure the permissions in whatever order we want.)<\/p>\n To add a principal to the SrvRole1<\/strong> server role, we can use the ALTER<\/strong> SERVER<\/strong> ROLE<\/strong> statement, as shown in the following example:<\/p>\n The statement includes the ADD<\/strong> MEMBER<\/strong> clause, which specifies the srv01b\\WinUser1<\/strong> server login. At this point, we could add other logins or user-defined roles, but we cannot add a fixed server role.<\/p>\n At times, you will likely want to be able to track which principals have been assigned to your roles. You can use SSMS or you can create a T-SQL query that retrieves data from the sys.server_principals<\/strong> and sys.server_role_members<\/strong> catalog views, as shown in the following example:<\/p>\n The sys.server_role_members<\/strong> catalog view merely matches the principal IDs of the server roles to their members. To get the information we need, we must join each column in the view to the principal_id<\/strong> column in the sys.server_principals<\/strong> catalog view. The statement also includes a WHERE<\/strong> clause to limit the results to the SrvRole1<\/strong> server role. As expected, the statement returns only the value srv01b\\WinUser1<\/strong>.<\/p>\n Creating a database role is just as simple as creating a server role. As before, we can use SSMS or issue a T-SQL statement, in this case, the CREATE<\/strong> ROLE<\/strong> statement. For instance, the following example creates the DbRole1<\/strong> database role:<\/p>\n To add a principal to the DbRole1<\/strong> database role, we can use the ALTER<\/strong> ROLE<\/strong> statement, as shown in the following example:<\/p>\n In this case, we\u2019ve added the DbUser1<\/strong> principal. Similar to server roles, we can add other database users or user-defined database roles, but not fixed database roles or any type of server principal.<\/p>\n SQL Server also include catalog views for retrieving information about database roles and principals. For example, the following SELECT<\/strong> statement uses the sys.database_principals<\/strong> and sys.database_role_members<\/strong> catalog views to retrieve the members of the DbRole1<\/strong> database role:<\/p>\n This statement is much like the one we used to retrieve details about the SrvRole1<\/strong> server role, only this case, its focus is on the DbRole1<\/strong> database role and consequently returns the value DbUser1<\/strong>.<\/p>\n Once we have our principals in place, we can start assigning them the permissions they need to access the various securables.<\/em> A securable is a specific SQL Server resource whose access is controlled by the database engine through the use of permissions.<\/p>\n SQL Server includes securables at three different scopes:<\/p>\n For each securable, SQL Server supports a set of permissions that are specific to that securable\u2019s scope and function. You can grant, deny, or revoke permissions on any securable to any principal, within the context of the specific principal and securable. For example, you can grant the SELECT<\/strong> permission on the Employee<\/strong> table to the DbUser1<\/strong> database user, which allows the user to retrieve data from that table.<\/p>\n SQL Server supports three T-SQL statements for configuring permissions on a principal:<\/p>\n Each of these statements supports a fairly complex syntax, coupled with the intricacies of the permission structure itself. A good place to start to more fully understand how permissions work is with the MSDN article Permissions (Database Engine)<\/a>. You might also want to review the SQL Server documentation about each of the permission-related statements:<\/p>\n Let\u2019s look at a few examples that demonstrate principals, securables, and permissions in action. We\u2019ll start by running an EXECUTE<\/strong> AS<\/strong> statement to change the execution context of our session to the DbUser1<\/strong> database user account in the AdventureWorks2014<\/strong> database:<\/p>\n Now let\u2019s try running the following T-SQL statements as DbUser1<\/strong>:<\/p>\n In each case, we will receive an error indicating that the user does not have the permissions necessary to access the requested resources. The reason is, of course, that the user has not been granted these permissions. Before we can do that, however, we must run a REVERT<\/strong> statement to return to the context of the user that does have the necessary permissions (the user you likely logged in as originally):<\/p>\n Now that we\u2019re back to our old selves, let\u2019s use a GRANT<\/strong> statement to grant the EXECUTE<\/strong> permission on the uspUpdateEmployeeHireInfo<\/strong> stored procedure to the DbRole1<\/strong> database role, which includes the DbUser1<\/strong> database user:<\/p>\n This example shows the GRANT<\/strong> statement in one of its simplest forms. However, as pointed out earlier, the statement supports a complex syntax that provides numerous options, depending on the securables and principals.<\/p>\n After granting the necessary permissions, we can rerun the previous EXECUTE<\/strong> AS<\/strong> statement to change the context back to DbUser1<\/strong> and then rerun the SELECT<\/strong> and EXECUTE<\/strong> statements. This time, we\u2019ll still receive errors when we try to run the SELECT<\/strong> statements, but the EXECUTE<\/strong> statement will run fine.<\/p>\n CREATE LOGIN [srv01b\\WinUser1] FROM WINDOWS;\r\n<\/pre>\n
Database user principals<\/h2>\n
USE AdventureWorks2014;\r\nGO\r\n\r\nCREATE USER DbUser1 FOR LOGIN [srv01b\\WinUser1];\r\n<\/pre>\n
Server and database roles<\/h2>\n
EXEC sp_helpsrvrole;\r\n<\/pre>\n
CREATE SERVER ROLE SrvRole1;\r\n<\/pre>\n
ALTER SERVER ROLE SrvRole1 ADD MEMBER [srv01b\\WinUser1];\r\n<\/pre>\n
SELECT m.name MemberName\r\nFROM sys.server_role_members rm \r\n JOIN sys.server_principals r\r\n ON rm.role_principal_id = r.principal_id\r\n JOIN sys.server_principals m\r\n ON rm.member_principal_id = m.principal_id\r\nWHERE r.name = 'SrvRole1'; \r\n<\/pre>\n
CREATE ROLE DbRole1;\r\n<\/pre>\n
ALTER ROLE DbRole1 ADD MEMBER DbUser1;\r\n<\/pre>\n
SELECT m.name MemberName\r\nFROM sys.database_role_members rm \r\n JOIN sys.database_principals r\r\n ON rm.role_principal_id = r.principal_id\r\n JOIN sys.database_principals m\r\n ON rm.member_principal_id = m.principal_id\r\nWHERE r.name = 'DbRole1'; \r\n<\/pre>\n
Securables and permissions<\/h2>\n
\n
\n
\n
EXECUTE AS USER = 'DbUser1';\r\n<\/pre>\n
SELECT * FROM HumanResources.Employee; \r\n\r\nSELECT * FROM HumanResources.EmployeePayHistory;\r\n\r\nSELECT * FROM HumanResources.JobCandidate; \r\n\r\nSELECT * FROM Person.Person; \r\n\r\nEXEC HumanResources.uspUpdateEmployeeHireInfo \r\n 5, 'Design Engineer II', '2008-01-06', '2016-07-14', 44.2938, 2, 1;\r\n<\/pre>\n
REVERT;\r\n<\/pre>\n
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo TO DbRole1; \r\n<\/pre>\n