{"id":83072,"date":"2019-01-28T15:35:22","date_gmt":"2019-01-28T15:35:22","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83072"},"modified":"2021-04-27T14:00:53","modified_gmt":"2021-04-27T14:00:53","slug":"introduction-to-sql-server-security-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/devops\/data-privacy-and-protection\/introduction-to-sql-server-security-part-2\/","title":{"rendered":"Introduction to SQL Server Security \u2014 Part 2"},"content":{"rendered":"<h4>The series so far:<\/h4>\n<ol>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/data-protection-and-privacy\/introduction-to-sql-server-security-part-1\/\">Introduction to SQL Server Security \u2014 Part 1<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/data-protection-and-privacy\/introduction-to-sql-server-security-part-2\/\">Introduction to SQL Server Security \u2014 Part 2<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/data-protection-and-privacy\/introduction-to-sql-server-security-part-3\/\">Introduction to SQL Server Security \u2014 Part 3<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/data-protection-and-privacy\/introduction-to-sql-server-security-part-4\">Introduction to SQL Server Security \u2014 Part 4<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/data-protection-and-privacy\/introduction-to-sql-server-security-part-5\">Introduction to SQL Server Security \u2014 Part 5<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/data-protection-and-privacy\/introduction-to-sql-server-security-part-6\/\">Introduction to SQL Server Security\u00a0<span>\u2014 <\/span>Part 6<\/a><\/li>\n<li style=\"list-style-type: none;\">\u00a0<\/li>\n<\/ol>\n\n<p>One of the most important tasks that DBAs must perform when managing a SQL Server database is to ensure that authorized users can access the data they need and carry out the necessary operations on that data. To this end, SQL Server includes a number of components for authenticating users and authorizing them to access objects at the server, database, and schema levels, while preventing unauthorized users from doing anything they should not.<\/p>\n<p>In the first article in this series, I introduced you to authentication and authorization as part of a larger discussion about SQL Server security. In this article, I dig deeper into these topics and provide some examples that demonstrate how to implement basic access controls on a SQL Server 2017 instance.<\/p>\n<p>Note, however, that authentication and authorization are huge topics. For this reason, you might also want to refer to a couple of other Simple Talk articles, one that I wrote and the other by Phil Factor:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/sql-server-access-control-basics\/\">SQL Server Access Control: The Basics<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/sql-training\/schema-based-access-control-for-sql-server-databases\/\">Schema-Based Access Control for SQL Server Databases<\/a><\/li>\n<\/ul>\n<p>You should also refer to Microsoft documentation as necessary to ensure you fully understand what types of access you\u2019re granting to your users and what tasks they can and cannot perform. A good place to start is with <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/security\/security-center-for-sql-server-database-engine-and-azure-sql-database?view=sql-server-2017\">Security Center for SQL Server Database Engine and Azure SQL Database<\/a>, which covers a number of important aspects of SQL Server security, including access control.<\/p>\n<h2>Getting Started with Authentication and Authorization<\/h2>\n<p>SQL Server provides three types of components for controlling which users can log onto SQL Server, what data they can access, and which operations they can carry out:<\/p>\n<ul>\n<li><strong>Principals:<\/strong> Individuals, groups, or processes granted access to the SQL Server instance, either at the server level or database level. Server-level principals include logins and server roles. Database-level principals include users and database roles.<\/li>\n<li><strong>Securables:<\/strong> Objects that make up the server and database environment. The objects can be broken into three hierarchical levels:\n<ul>\n<li>Server-level securables include such objects as databases and availability groups.<\/li>\n<li>Database-level securables include such objects as schemas and full-text catalogs.<\/li>\n<li>Schema-level securables include such objects as tables, views, functions, and stored procedures.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Permissions:<\/strong> The types of access permitted to principals on specific securables. You can grant or deny permissions to securables at the server, database, or schema level. The permissions you grant at a higher level of the hierarchy also apply to children and grandchildren objects, unless you specifically deny those permissions at the lower level.<\/li>\n<\/ul>\n<p>Together, these three component types provide a structure for authenticating and authorizing SQL Server users. You must grant each principal the appropriate permissions it needs on specific securables to enable users to access SQL Server resources. For example, if the <code>sqluser01<\/code> database user needs to be able to query data in the <code>Sales<\/code> schema, you can grant the <code>SELECT<\/code> permission to that user on the schema. The user would then be able to query each table and view within the schema.<\/p>\n<p>One way to check effective permissions without writing scripts yourself is to use Redgate\u2019s <a title=\"https:\/\/www.redgatefoundry.com\/apps\/sql-census\" href=\"https:\/\/www.redgatefoundry.com\/apps\/sql-census\">SQL Census<\/a>. It creates a report of who has access to what on your SQL Servers and helps you improve your overall security model, too. It\u2019s still in development so doesn\u2019t yet give a comprehensive view down to object level, but it\u2019s a good starting point to check on your SQL Server permissions and undertake any necessary cleaning tasks.<\/p>\n<p>In most cases, you\u2019ll take some or all of the following steps to provide users with the access they need to SQL Server resources:<\/p>\n<ol>\n<li>At the server level, create a login for each user that should be able to log into SQL Server. You can create Windows authentication logins that are associated with Windows user or group accounts, or you can create SQL Server authentication logins that are specific to that instance of SQL Server.<\/li>\n<li>Create user-defined server roles if the fixed server roles do not meet your configuration requirements.<\/li>\n<li>Assign logins to the appropriate server roles (either fixed or user-defined).<\/li>\n<li>For each applicable server-level securable, grant or deny permissions to the logins and server roles.<\/li>\n<li>At the database level, create a database user for each login. A database user can be associated with only one server login. You can also create database users that are not associated with logins, in which case, you can skip the first four steps.<\/li>\n<li>Create user-defined database roles if the fixed database roles do not meet your configuration requirements.<\/li>\n<li>Assign users to the appropriate database roles (either fixed or user-defined).<\/li>\n<li>For each applicable database-level or schema-level securable, grant or deny permissions to the database users and roles.<\/li>\n<\/ol>\n<p>You will not necessarily have to carry out all these steps, depending on your particular circumstances. For example, you might not need to create any user-defined roles at the server or database levels. In addition, you do not need to follow these steps in the exact order. You might grant permissions to server logins or database users when you create them, or you might create server roles and database roles before creating the logins or users. The steps listed here are meant only as a guideline.<\/p>\n<p>The examples in the following sections walk you through the process of creating principals and assigning permissions to them for specific securables. All the examples use T-SQL to carry out these operations. You can also use features built into the SQL Server Management Studio (SSMS) interface to perform many of these tasks, but knowing the T-SQL can make it easier to repeat steps and add them to your scripts.<\/p>\n<h2>Creating Server Logins<\/h2>\n<p>SQL Server supports four types of logins: Windows, SQL Server, certificate-mapped, and asymmetric key-mapped. For this article, I focus on Windows and SQL Server logins, using the <code>CREATE<\/code> <code>LOGIN<\/code> statement to define several logins. Because logins exist at the server level, you must create them within the context of the <code>master<\/code> database.<\/p>\n<p>A Windows login is associated with a local Windows account or domain account. When you create the login, you must specify the Windows account, preceded by the computer name or domain name and a backslash. For example, the following <code>CREATE<\/code> <code>LOGIN<\/code> statement defines a login based on the <code>winuser01<\/code> local user account on the <code>win10b<\/code> computer:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE master;\r\nGO\r\nCREATE LOGIN [win10b\\winuser01] FROM WINDOWS \r\nWITH DEFAULT_DATABASE = master, DEFAULT_LANGUAGE = us_english;\r\nGO<\/pre>\n<p>The statement must include the <code>FROM<\/code> <code>WINDOWS<\/code> clause to indicate that this is a Windows login. In this case, the statement also includes an optional <code>WITH<\/code> clause, which specifies a default database and language.<\/p>\n<p>If you\u2019re creating a login based on a domain account, replace the computer name with the domain name, following the same format:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">[&lt;domain_name&gt;\\&lt;windows_account&gt;]<\/pre>\n<p>You should also use this format if creating a login based on a Windows group. For example, the following <code>CREATE<\/code> <code>LOGIN<\/code> statement creates a login based on <code>wingroup01<\/code>, a group defined on the local Windows computer:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE LOGIN [win10b\\wingroup01] FROM WINDOWS \r\nWITH DEFAULT_DATABASE = master, DEFAULT_LANGUAGE = us_english;\r\nGO<\/pre>\n<p>By creating a login based on a group, you can provide the same level of access to any user within that group, while letting Windows and SQL Server handle authenticating and authorizing the individual users.<\/p>\n<p>You can also use the <code>CREATE<\/code> <code>LOGIN<\/code> statement to define a SQL Server login (one that is not associated with a Windows account), in which case, do not include the <code>FROM<\/code> <code>WINDOWS<\/code> clause. However, you must include a <code>WITH<\/code> clause that specifies a password, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE LOGIN sqluser01 \r\nWITH PASSWORD = 'tempPW@56789' \r\n  MUST_CHANGE, CHECK_EXPIRATION = ON,\r\n  DEFAULT_DATABASE = master, DEFAULT_LANGUAGE = us_english;\r\nGO<\/pre>\n<p>For the password, you can provide a string value, as I\u2019ve done here, or a hashed value, along with the <code>HASH<\/code> keyword. You can also define additional options. In this case, the <code>WITH<\/code> clause includes the <code>MUST_CHANGE<\/code> option to force the user to change the password when first logging into SQL Server. The clause also sets the <code>CHECK_EXPIRATION<\/code> option to <code>ON<\/code>, which means that the password expiration policy will be enforced on this login.<\/p>\n<p>Once you\u2019ve created a login, you can use the <code>GRANT<\/code> statement to grant permissions to that login. For example, the following statement grants the <code>IMPERSONATE<\/code> <code>ANY<\/code> <code>LOGIN<\/code> permission to the <code>winuser01<\/code> and <code>sqluser01<\/code> users, allowing them to run T-SQL statements within the context of another user:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">GRANT IMPERSONATE ANY LOGIN TO [win10b\\winuser01], sqluser01;\r\nGO<\/pre>\n<p>After you\u2019ve granted permissions to a principal, you can use the <code>sys.server_principals<\/code> and <code>sys.server_permissions<\/code> catalog views to verify that the permissions have been configured correctly:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT pr.principal_id, pr.name, pe.state_desc, pe.permission_name  \r\nFROM sys.server_principals pr INNER JOIN sys.server_permissions pe \r\n  ON pr.principal_id = pe.grantee_principal_id\r\nWHERE pr.principal_id = SUSER_ID('win10b\\winuser01')\r\n  OR pr.principal_id = SUSER_ID('sqluser01');<\/pre>\n<p>The <code>SELECT<\/code> statement joins the two catalog views and filters the results by the two logins, using the <code>SUSER_ID<\/code> built-in function to retrieve each login\u2019s principal identification number. Figure 1 shows the data returned by the <code>SELECT<\/code> statement.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"480\" height=\"162\" class=\"wp-image-83073\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/01\/word-image-142.png\" \/><\/p>\n<p class=\"caption\">Figure 1. Viewing permissions assigned to SQL Server principals<\/p>\n<p>The results show that both users have been assigned the <code>IMPERSONATE<\/code> <code>ANY<\/code> <code>LOGIN<\/code> permission, along with the <code>CONNECT<\/code> <code>SQL<\/code> permission, which is assigned by default to all logins to enable them to connect to the SQL Server instance.<\/p>\n<h2>Creating Server Roles<\/h2>\n<p>A server role makes it possible for you to group logins together in order to more easily manage server-level permissions. SQL Server supports fixed server roles and user-defined server roles. You can assign logins to a fixed server role, but you cannot change its permissions. You can do both with a user-defined server role.<\/p>\n<p>Creating and configuring a user-defined server role is very straightforward. You create the role, grant permissions to the role, and then add logins\u2014or you can add the logins and then grant the permissions. The following T-SQL takes the first approach:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE SERVER ROLE devops;\r\nGRANT ALTER ANY DATABASE TO devops; \r\nALTER SERVER ROLE devops ADD MEMBER [win10b\\winuser01];\r\nGO<\/pre>\n<p>The <code>CREATE<\/code> <code>SERVER<\/code> <code>ROLE<\/code> statement defines a server role named <code>devops<\/code>. If you want to specify an owner for the server role, you can include an <code>AUTHORIZATION<\/code> clause. Without the clause, the login that executes that statement becomes the owner.<\/p>\n<p>The <code>GRANT<\/code> statement grants the <code>ALTER<\/code> <code>ANY<\/code> <code>DATABASE<\/code> permission to the <code>devops<\/code> role, which means that any members of that role will acquire that permission. The <code>ALTER<\/code> <code>SERVER<\/code> <code>ROLE<\/code> statement adds the <code>winuser01<\/code> login to the <code>devops<\/code> role.<\/p>\n<p>That\u2019s all there is to it. You can then use the <code>sys.server_principals<\/code> and <code>sys.server_permissions<\/code> catalog views to verify that the permissions on the <code>devops<\/code> role have been set up correctly:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT pe.state_desc, pe.permission_name  \r\nFROM sys.server_principals pr INNER JOIN sys.server_permissions pe \r\n  ON pr.principal_id = pe.grantee_principal_id\r\nWHERE pr.principal_id = SUSER_ID('devops');<\/pre>\n<p>The statement\u2019s results should confirm that that <code>devops<\/code> role has been granted the <code>ALTER<\/code> <code>ANY<\/code> <code>DATABASE<\/code> permission.<\/p>\n<p>You can also confirm that the <code>winuser01<\/code> login has been added to the <code>devops<\/code> role by using the <code>sys.server_role_members<\/code> and <code>sys.server_principals<\/code> catalog views:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT rm.member_principal_id, pr.name  \r\nFROM sys.server_role_members rm INNER JOIN sys.server_principals pr \r\n  ON rm.member_principal_id = pr.principal_id\r\nWHERE rm.role_principal_id = SUSER_ID('devops');<\/pre>\n<p>The results from the <code>SELECT<\/code> statement should indicate that the <code>winuser01<\/code> login has been added to the <code>devops<\/code> role and that no other logins are included. Of course, in a real-world scenario, you would be adding multiple logins to a user-defined server role. Otherwise, there would be little reason to create it.<\/p>\n<h2>Creating Database Users<\/h2>\n<p>After you\u2019ve set up your server-level logins, you can create database users that map back to those logins, whether they\u2019re Windows or SQL Server logins. You can also create database users that do not map to logins. These types of logins are generally used for contained databases, impersonation, or development and testing.<\/p>\n<p>SQL Server provides the <code>CREATE<\/code> <code>USER<\/code> statement for creating database users. You must run this statement within the context of the database in which the user is being defined. For example, the following T-SQL creates a user in the <code>WideWorldImporters<\/code> database and then assigns the <code>ALTER<\/code> permission to the user on the <code>Sales<\/code> schema:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE WideWorldImporters;\r\nGO\r\nCREATE USER [win10b\\winuser01];\r\nGRANT ALTER ON SCHEMA::Sales TO [win10b\\winuser01]; \r\nGO<\/pre>\n<p>The <code>winuser01<\/code> user is based on the <code>win10b\\winuser01<\/code> login. When you create a database user that has the same name as a login, you do not need to specify the login. However, if you want to create a user with a different name, you must include the <code>FOR<\/code> <code>LOGIN<\/code> or <code>FROM<\/code> <code>LOGIN<\/code> clause, as in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE USER winuser03 FOR LOGIN [win10b\\winuser01];\r\nGRANT ALTER ON SCHEMA::Sales TO winuser03; \r\nGO<\/pre>\n<p>You can create only one user in a database per login. If you want to try out both these statements, you\u2019ll need to drop the first user before creating the second. The examples that follow are based on the <code>win10b\\winuser01<\/code> user.<\/p>\n<p>The two preceding examples also include a <code>GRANT<\/code> statement that assigns the <code>ALTER<\/code> permission to the user on the <code>Sales<\/code> schema. As a result, the user will be able to alter any object within that schema. Notice that the statement includes the <code>SCHEMA::Sales<\/code> element. When you grant a permission on a specific object, you must specify the type of object and its name, separated by the scope qualifier (double colons).<\/p>\n<p>In some <code>GRANT<\/code> statements, the securable is implied, so it does not need to be specified. For instance, in an earlier example, you granted the <code>ALTER<\/code> <code>ANY<\/code> <code>DATABASE<\/code> permission to the <code>devops<\/code> role. Because you granted this permission at the server level for all database objects at that level, you did not need to specify a securable.<\/p>\n<p>After you\u2019ve granted permissions to a database user, you can use the <code>sys.database_principals<\/code> and <code>sys.database_permissions<\/code> catalog views to verify that the permissions have been configured correctly:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT pe.state_desc, pe.permission_name  \r\nFROM sys.database_principals pr INNER JOIN sys.database_permissions pe \r\n  ON pr.principal_id = pe.grantee_principal_id\r\nWHERE pr.principal_id = USER_ID('win10b\\winuser01');<\/pre>\n<p>Notice that the <code>WHERE<\/code> clause uses the <code>USER_ID<\/code> function and not the <code>SUSER_ID<\/code> function, which was used in the earlier examples. The <code>USER_ID<\/code> function returns the user principal ID, rather than the login principal ID. Figure 2 shows the results returned by the <code>SELECT<\/code> statement. In addition to the <code>ALTER<\/code> permission, the user is automatically granted the <code>CONNECT<\/code> permission:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"263\" height=\"125\" class=\"wp-image-83074\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/01\/word-image-143.png\" \/><\/p>\n<p class=\"caption\">Figure 2. Viewing permissions assigned to winuser01<\/p>\n<p>Creating a database user that\u2019s associated with a SQL Server login is just as simple as creating a user based on a Windows login, especially when you use the same name, as in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE USER sqluser01;\r\nGO<\/pre>\n<p>The <code>CREATE<\/code> <code>USER<\/code> statement creates the <code>sqluser01<\/code> user, but this time, the example grants no permissions. As a result, the user receives only the <code>CONNECT<\/code> permission, which you can verify by running the following <code>SELECT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT pe.state_desc, pe.permission_name  \r\nFROM sys.database_principals pr INNER JOIN sys.database_permissions pe \r\n  ON pr.principal_id = pe.grantee_principal_id\r\nWHERE pr.principal_id = USER_ID('sqluser01');<\/pre>\n<p>You can also create a user based on a Windows account even if you don\u2019t create a login. For example, the following statement creates the <code>winuser02<\/code> user that\u2019s associated with the <code>win10b\\winuser02<\/code> account on the local computer:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE USER [win10b\\winuser02];\r\nGO<\/pre>\n<p>Creating a user in this way makes it possible to support contained databases, which do not use server logins. Once again, you can verify that the user has been granted only the <code>CONNECT<\/code> permission by running the following <code>SELECT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT pe.state_desc, pe.permission_name  \r\nFROM sys.database_principals pr INNER JOIN sys.database_permissions pe \r\n  ON pr.principal_id = pe.grantee_principal_id\r\nWHERE pr.principal_id = USER_ID('win10b\\winuser02');<\/pre>\n<p>SQL Server also lets you create a user that is not associated with either a login or Windows account. To do so, you must include the <code>WITHOUT<\/code> <code>LOGIN<\/code> clause, as shown in the following example.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE USER sqluser02 WITHOUT LOGIN;\r\nGO<\/pre>\n<p>Creating a user without a login can be useful for development and testing. More importantly, it can be used with SQL Server\u2019s impersonation capabilities. Users can authenticate to SQL Server under their own credentials and then impersonate the user account that\u2019s not associated with a login. In this way, the authentication process can be monitored, but specific types of permissions can be granted to the unassociated user.<\/p>\n<h2>Creating Database Roles<\/h2>\n<p>A database role is a group of users that share a common set of database-level permissions. As with server roles, SQL Server supports both fixed and user-defined database roles. To set up a user-defined database role, you must create the role, grant permissions to the role, and add members to the role (or add members and then grant permissions). The following example demonstrates how to set up the <code>dbdev<\/code> role:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE ROLE dbdev;\r\nGRANT SELECT ON DATABASE::WideWorldImporters TO dbdev;\r\nALTER ROLE dbdev ADD MEMBER [win10b\\winuser01];\r\nALTER ROLE dbdev ADD MEMBER sqluser01;\r\nGO<\/pre>\n<p>The <code>CREATE<\/code> <code>ROLE<\/code> statement creates the database role. The <code>GRANT<\/code> statement grants the role the <code>SELECT<\/code> permission on the database. The two <code>ALTER<\/code> <code>ROLE<\/code> statements add the <code>winuser01<\/code> and <code>sqluser01<\/code> users to the role.<\/p>\n<p>You can verify that the <code>SELECT<\/code> permission has been granted to the role by running the following <code>SELECT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT pe.state_desc, pe.permission_name  \r\nFROM sys.database_principals pr INNER JOIN sys.database_permissions pe \r\n  ON pr.principal_id = pe.grantee_principal_id\r\nWHERE pr.principal_id = USER_ID('dbdev');<\/pre>\n<p>In some cases, you might want to see the effective (cumulative) permissions granted to a principal on a securable. A simple way to do this is to use the <code>fn_my_permissions<\/code> table-valued function, specifying the securable and its type.<\/p>\n<p>The trick to using this function is to call it within the execution context of the specific user. To do so, you must first issue an <code>EXECUTE<\/code> <code>AS<\/code> statement and then, after running your <code>SELECT<\/code> statement, issue a <code>REVERT<\/code> statement, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'win10b\\winuser01'; \r\nSELECT * FROM fn_my_permissions ('Sales.BuyingGroups', 'OBJECT'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>The <code>fn_my_permissions<\/code> function takes two arguments: the target securable and the type of securable. In this case, the target securable is the <code>Sales.BuyingGroups<\/code> table, and the securable type is <code>OBJECT<\/code>, which includes schema-level securables such as tables, views, and stored procedures. Figure 3 shows the results returned by the <code>SELECT<\/code> statement.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"402\" height=\"212\" class=\"wp-image-83075\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/01\/word-image-144.png\" \/><\/p>\n<p class=\"caption\">Figure 3. Viewing effective permissions for winuser01<\/p>\n<p>As you\u2019ll recall from the previous section, the <code>ALTER<\/code> permission was granted to <code>winuser01<\/code> after the user was created, and the <code>SELECT<\/code> permission was granted to the role after it was created. Notice that each of the table\u2019s columns is also assigned the <code>SELECT<\/code> permission.<\/p>\n<p>Now run the same <code>SELECT<\/code> statement within the execution context of the <code>sqluser01<\/code> user:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales.BuyingGroups', 'OBJECT'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>The <code>SELECT<\/code> statement returns the results shown in Figure 4, which are specific to the user specified in the <code>EXECUTE<\/code> <code>AS<\/code> statement.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"407\" height=\"200\" class=\"wp-image-83076\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/01\/word-image-145.png\" \/><\/p>\n<p class=\"caption\">Figure 4. Viewing effective permissions for sqluser01<\/p>\n<p>This time, the <code>ALTER<\/code> permission is not included in the results because that permission was never granted to that user.<\/p>\n<h2>Digging into Permissions<\/h2>\n<p>Several of the examples so far have used the <code>GRANT<\/code> statement to assign permissions to principals, but SQL Server actually provides three T-SQL statements for working with permissions:<\/p>\n<ul>\n<li>Use a <code>GRANT<\/code> statement to enable principals to access specific securables.<\/li>\n<li>Use a <code>DENY<\/code> statement to prevent principals from accessing specific securables. A <code>DENY<\/code> statement overrides any granted permissions.<\/li>\n<li>Use a <code>REVOKE<\/code> statement to remove permissions that have been granted to principals on specific securables.<\/li>\n<\/ul>\n<p>Permissions are cumulative in that the user receives all permissions granted specifically to the database user as well as to its associated login. Also, if the user has been assigned to a database role or if the login has been assigned to a server role, the user receives the role permissions as well.<\/p>\n<p>Permissions are also transitive, based on the hierarchical nature of the server, database, and schema securables. For example, if you grant the <code>UPDATE<\/code> permission to a user for a specific database, the user will also be granted the <code>UPDATE<\/code> permission on all schemas and schema objects such as tables and views.<\/p>\n<p>In addition, some permissions are covering, that is, they include multiple permissions under a single name. A good example of this is the <code>CONTROL<\/code> permission, which includes such permissions as <code>INSERT<\/code>, <code>UPDATE<\/code>, <code>DELETE<\/code>, <code>EXECUTE<\/code>, and several others. For instance, the following <code>GRANT<\/code> statement grants the <code>CONTROL<\/code> permission to <code>sqluser01<\/code> for the <code>Sales<\/code> schema:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">GRANT CONTROL ON SCHEMA::Sales TO sqluser01;<\/pre>\n<p>After granting the <code>CONTROL<\/code> permission, you can again use the <code>fn_my_permissions<\/code> function to view the effective permissions for that user on the <code>Sales<\/code> schema:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales', 'SCHEMA'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>Figure 5 shows the results returned by the <code>SELECT<\/code> statement.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"403\" height=\"308\" class=\"wp-image-83077\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/01\/word-image-146.png\" \/><\/p>\n<p class=\"caption\">Figure 5. Viewing effective permissions on the Sales schema<\/p>\n<p>The <code>SELECT<\/code> permission was granted to the user at the database level through the <code>dbdev<\/code> role and at the schema level as part of the <code>CONTROL<\/code> permission, as are the rest of the permissions shown in the results. You can also view the effective permissions on an object within the <code>Sales<\/code> schema by using the following <code>SELECT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales.BuyingGroups', 'OBJECT'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>In this case, the <code>fn_my_permissions<\/code> function specifies the <code>BuyingGroups<\/code> table as the target object. As a result, the <code>SELECT<\/code> statement now returns 25 rows of permissions on that table for <code>sqluser01<\/code>. Figure 6 shows the first 13 rows from that result set.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"442\" height=\"316\" class=\"wp-image-83078\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/01\/word-image-147.png\" \/><\/p>\n<p class=\"caption\">Figure 6. Viewing effective permissions on the BuyingGroups table<\/p>\n<p>As you can see, covering permissions help simplify the process of granting access to the database objects. Without them, your <code>GRANT<\/code> statements would look more like the following:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, \r\nEXECUTE, CREATE SEQUENCE, VIEW CHANGE TRACKING, \r\nVIEW DEFINITION, ALTER, TAKE OWNERSHIP, CONTROL\r\nON SCHEMA::Sales TO sqluser01;<\/pre>\n<p>You can also deny permissions on securables. This can be useful when you want to grant permissions at a higher level in the object hierarchy but want to prevent those permissions from extending to a few of the child objects. For example, you can deny the <code>CONTROL<\/code> permission to <code>sqluser01<\/code> on an individual table within the <code>Sales<\/code> schema, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DENY CONTROL ON OBJECT::Sales.BuyingGroups TO sqluser01;<\/pre>\n<p>When you deny the <code>CONTROL<\/code> permission, you deny all permissions that are part of <code>CONTROL<\/code>, including the <code>SELECT<\/code> permission. You can verify this by running the following <code>SELECT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales.BuyingGroups', 'OBJECT'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>The <code>SELECT<\/code> statement returns an empty result set, indicating that <code>sqluser01<\/code> no longer has any type of permissions on the <code>BuyingGroups<\/code> table.<\/p>\n<p>The <code>DENY<\/code> permission takes precedence over all granted permissions, no matter where in the object hierarchy permissions are granted or denied. However, denying permissions on one object does not impact other objects unless they\u2019re child objects. For example, the following <code>SELECT<\/code> statement shows that all permissions are still intact on the <code>CustomerCategories<\/code> table in the <code>Sales<\/code> schema:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales.CustomerCategories', 'OBJECT'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>However, if you deny permissions on an object that contains child objects, the permissions are also denied on the child objects. For instance, the following <code>DENY<\/code> statement denies <code>sqluser01<\/code> the <code>ALTER<\/code> permission on the <code>Sales<\/code> schema:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DENY ALTER ON SCHEMA::Sales TO sqluser01;<\/pre>\n<p>If you now run the following <code>SELECT<\/code> statement, you\u2019ll find that the <code>ALTER<\/code> permission is no longer granted at the <code>Sales<\/code> schema:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales', 'SCHEMA'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>You\u2019ll get the same results if you check the effective permissions on one of the objects in the <code>Sales<\/code> schema, such as the <code>CustomerCategories<\/code> table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales.CustomerCategories', 'OBJECT'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>Once again, the <code>ALTER<\/code> permission is no longer listed.<\/p>\n<p>In some cases, you will need to roll back the permissions that have been granted on an executable, in which case, you can use the <code>REVOKE<\/code> statement. For example, the following <code>REVOKE<\/code> statement removes the <code>CONTROL<\/code> permission from the <code>Sales<\/code> schema for <code>sqluser01<\/code>:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">REVOKE CONTROL ON SCHEMA::Sales TO sqluser01;<\/pre>\n<p>After revoking the <code>CONTROL<\/code> permission, you can once again use the <code>fn_my_permissions<\/code> function to view the effective permissions for that user on the <code>Sales<\/code> schema:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales', 'SCHEMA'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>This time, only the <code>SELECT<\/code> permission is listed. That\u2019s because this permission was granted separately at the database level as part of the <code>dbdev<\/code> role. You can also verify the effective permissions on the <code>CustomerCategories<\/code> table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXECUTE AS USER = 'sqluser01'; \r\nSELECT * FROM fn_my_permissions ('Sales.CustomerCategories', 'OBJECT'); \r\nREVERT;  \r\nGO  <\/pre>\n<p>Once again, the results indicate that only the <code>SELECT<\/code> permission has been granted on this table, as shown in Figure 7.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"452\" height=\"204\" class=\"wp-image-83079\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/01\/word-image-148.png\" \/><\/p>\n<p class=\"caption\">Figure 7. Viewing effective permissions on the CustomerCategories table<\/p>\n<p>When working with permissions, be careful not to confuse the <code>DENY<\/code> statement with the <code>REVOKE<\/code> statement. You could end up unintended consequences when users receive permissions from multiple sources, as in the examples above. For example, if you had denied <code>sqluser01<\/code> the <code>CONTROL<\/code> permission to the <code>Sales<\/code> schema, rather than revoke the permission, the user would no longer have <code>SELECT<\/code> permissions to the schema and its objects.<\/p>\n<h2>Controlling Access to SQL Server Data<\/h2>\n<p>Controlling access to SQL Server becomes an increasingly complex process as more users are added and the data structure itself becomes more complicated. Your goal should be to limit users to the least amount of privileges they need to do their jobs. Don\u2019t grant the <code>CONTROL<\/code> permission on the database when they need only the <code>SELECT<\/code> permission on a couple of tables. At the same time, don\u2019t make more work for yourself than necessary. If a user needs the <code>SELECT<\/code> permission on all tables in a schema, grant the permission at the schema level.<\/p>\n<p>SQL Server provides the ability to grant users the access they need at the level they need it. The <code>GRANT<\/code>, <code>DENY<\/code>, and <code>REVOKE<\/code> statements\u2014along with the wide assortments of permissions (230 in SQL Server 2016 and 237 in SQL Server 2017)\u2014make it possible to implement controls at a very granular level, while still providing the flexibility necessary to accommodate access at a higher level in the object hierarchy. However, controlling access takes careful planning and implementation. This is not the time for shortcuts or casual one-offs. The more diligently you control data access, the better for everyone and the more precise the control you have over the data.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this article, the second in the series, Robert Sheldon demonstrates how to manage the SQL Server security with granting permissions to users, logins, and groups.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143514,53],"tags":[68855],"coauthors":[6779],"class_list":["post-83072","post","type-post","status-publish","format-standard","hentry","category-data-privacy-and-protection","category-featured","tag-sql-provision"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83072"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83072\/revisions"}],"predecessor-version":[{"id":83650,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83072\/revisions\/83650"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83072"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}