SQL Server Permissions – Database Roles

EZ PZ Permission Squeezee

Given the critical level of importance related to permissions, one may think it is a concept that is well understood by all who are given the charge of protecting the data.

There is a great responsibility when the keys to the data kingdom are granted to a DBA. The DBA is responsible for ensuring the integrity and security of the data. To ensure the security of the data, the DBA has the responsibility of granting, revoking, or denying access, at various levels, to the data and objects within the database.

Despite this high visibility and critical nature of this concept, understanding permissions and assigning permissions does not seem to be as straight forward as it should be. Evidence of this is something I see far too frequently in the wild  as illustrated by the following image.

This screenshot is only a part of the problem that I wish to break down and discuss in this article.

SQL Server Permissions

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. In a previous article, I outlined the different flavors of principals while focusing primarily on the users and logins. You can brush up on that article here. While I touched lightly, in that article, on the concept of roles, I will expound on the roles a bit more here – but primarily in the scope of the effects on user permissions due to membership in various default roles.

Let’s reset back to the driving issue in the introduction. Frequently, I see what I would call a gross misunderstanding of permissions by way of how people assign permissions and role membership within SQL Server. The assignment of role membership does not stop with database roles. Rather it is usually combined with a mis-configuration of the server role memberships as well. This misunderstanding can really be broken down into one of the following errors:

• The belief that a login cannot access a database unless added specifically to the database.
• The belief that a login must be added to every database role.
• The belief that a login must be added to the sysadmin role to access resources in a database.

The experienced professional will likely note that there is a direct conflict between a few of these beliefs. That said, all too often I see all three of these misconceptions implemented in every instance for nearly every user.

Let’s start looking at these misconceptions. To investigate these problems, I will create a login. After creating the login, I will add that login as a member to the sysadmin role. Once the login is added to the sysadmin role, I will then run some simple tests within my DBA database.

Sysadmin

The creation of a server principal (login) and adding the principal to the sysadmin role is fairly simple. The next couple of screenshots are followed by a quick script that will perform the same actions.

As was promised, here is the script that will do the same thing as illustrated in the GUI above.

USE [master]
GO
CREATE LOGIN [superuser] WITH PASSWORD=N'ihavethepower', DEFAULT_DATABASE=[DBA], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [superuser]
GO

With the user now in place, let’s test. The primary test here would be that a server principal cannot access the database since explicit database permissions have not been granted. Here is the first batch of tests that I will run.

/* can i access the DBA database? */EXECUTE AS LOGIN = 'superuser'
USE DBA;
GO
SELECT *
FROM sys.objects o
WHERE o.name = 'MySuperTable';
SELECT *
FROM sys.database_principals dp
WHERE name = 'superuser';
SELECT SUSER_NAME(),*
FROM fn_my_permissions(NULL,NULL);
/* one more check for giggles */EXECUTE sys.SP_HELPROTECT @username = 'superuser'

The first statement is to allow me to impersonate the superuser login. From the impersonated connection, I first check to see I can query the sys.objects system catalog. Then I test the database_principals system catalog. Next in line is to check the list of permissions that have been granted to the superuser account. Each of these queries executes successfully without error. Here is a screen grab for these first three tests.

Notice the first two queries returned an empty set. This is not a failure, rather evidence that the select statement ran successfully. In the third result set, we can see that the superuser account has all sorts of server level permissions. In the result set there was not a single database level permission.

The last query that utilized sp_helprotect returned the following error:

Msg 15330, Level 11, State 1, Procedure sys.SP_HELPROTECT, Line 302
There are no matching rows on which to report.

This is confirmation that there is no database user called superuser.

So I can query a database without my server principal being given direct access to the database (it is worth reiterating here that this principal is in the sysadmin server role), but can I do other things such as create objects? Let’s test that with the following script.

/* can i create a table? */DROP TABLE IF EXISTS MySuperTable;
CREATE TABLE MySuperTable(superid BIGINT IDENTITY(1,1), SuperHero VARCHAR(128))
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS ObjectName
FROM sys.objects o
WHERE o.name = 'MySuperTable';

This script is straight forward. All it does is check for a table. If that table exists, then drop it and recreate it. The last little bit will check to confirm the successful creation of the table. This script succeeds as illustrated in the following image.

That should be pretty convincing that if you add a server principal to the sysadmin server role then that user has access to the databases. These tests have illustrated that it is not necessary to add a server principal as a database principal when that server principal is in the sysadmin role (an erroneous configuration often seen). If the database principal is not necessary in this case, then what will happen if a database principal does exist?

Database Principal in Every Database Role

The next logical step in the sequence is to create a database principal for the already created superuser server principal. Once created, we will test to see what effects if any can be observed by having this database principal in every database role as well as the sysadmin role. This will help to test the first two bullet items from the list of common configurations I have seen in the wild. Let’s start with the script that will help create the principal to be used during the next iteration of tests.

/* now let's do something that people love to do when assigning permissions
*/USE [DBA]
GO
CREATE USER [superuser] FOR LOGIN [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_datareader] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_denydatareader] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_denydatawriter] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_owner] ADD MEMBER [superuser]
GO
USE [DBA]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [superuser]
GO

The script basically creates a database principal and then adds that principal to each of the default fixed database roles available in SQL Server. Those roles are easily viewed in the first image in this article and are also listed here for ease of reference (intentionally ignoring the public role).

• db_owner
• db_accessadmin
• db_securityadmin
• db_ddladmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawriter

The tests for this round of changes will be just like in the previous section. Here is the script to be used for the first batch of tests.

/* can i access the DBA database? */EXECUTE AS LOGIN = 'superuser'
USE DBA;
GO
SELECT *
FROM sys.objects o
WHERE o.name = 'MySuperTable';
SELECT *
FROM sys.database_principals dp
where dp.name = 'superuser';
SELECT SUSER_NAME(),*
FROM fn_my_permissions(NULL,NULL);
/* one more check for giggles */EXECUTE sys.SP_HELPROTECT @username = 'superuser'

The major differences between this version of the test and the previous iteration of the test is that I have the table still in existence (I did not drop it but that will come shortly) and I have created a database principal so the first two queries will show a single row for each instead of an empty result set. The next significant difference is the last query that utilizes sp_helprotect. Instead of an error like the first time, this execution gives me the following results.

Next I will rerun the test to create an object with the following script:

/* can i create a table? */DROP TABLE IF EXISTS MySuperTable;
CREATE TABLE MySuperTable(superid BIGINT IDENTITY(1,1), SuperHero VARCHAR(128))
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS ObjectName
FROM sys.objects o
WHERE o.name = 'MySuperTable';

This script will produce the same results as in the first example. The table, since it was already there, will be dropped and then recreated. After it is recreated, the validation script will find the table and return a single row.

This series of tests has just shown that a database principal tied to a login which is added to the sysadmin role and then added to all database roles will still be able to do pretty much everything a sysadmin can do. To this point, I have shown how bullet points one and three are not sound in reasoning. With that, there is still the test to prove (true or false) the final bullet point that a principal should be added to every database role.

Before testing the validity of the configuration, I want you to look closely at the list of database roles.

• db_owner
• db_accessadmin
• db_securityadmin
• db_ddladmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawriter

Just reading the names of these roles should be a good indicator that adding a user to every database role is not a desired configuration. I am sure the question is bubbling up now in regards to some of those “but how is it working in the previous examples?”. The response to that is very easy: “The user is a sysadmin!”.

To test this last bit of the configuration, it is time to remove the server principal from the sysadmin role. I will do that via the following script.

/* now remove from sysadmin */ALTER SERVER ROLE [sysadmin] DROP MEMBER [superuser]
GO

With the user removed from the sysadmin role, it is time to repeat the tests.

/* can i access the DBA database? */EXECUTE AS LOGIN = 'superuser'
USE DBA;
GO
SELECT *
FROM sys.objects o
WHERE o.name = 'MySuperTable';
SELECT *
FROM sys.database_principals dp
WHERE dp.name = 'superuser';

The results are significantly different this time.

Msg 229, Level 14, State 5, Line 132
The SELECT permission was denied on the object ‘objects’, database ‘mssqlsystemresource’, schema ‘sys’.
Msg 229, Level 14, State 5, Line 136
The SELECT permission was denied on the object ‘database_principals’, database ‘mssqlsystemresource’, schema ‘sys’.

I just tried to query two objects and was refused due to a denial in each case. What if I try to query that table I created previously?

/* query the supertable that superuser created*/SELECT *
FROM dbo.MySuperTable;

This will give me the following error.

Msg 229, Level 14, State 5, Line 141
The SELECT permission was denied on the object ‘MySuperTable’, database ‘DBA’, schema ‘dbo’.

Well that is not the desired effect or is it? Remember, I added the superuser principal to every role and that includes the “deny” roles.

How about if I try to drop and recreate that table?

/* can i create a table? */DROP TABLE IF EXISTS MySuperTable;
CREATE TABLE MySuperTable(superid BIGINT IDENTITY(1,1), SuperHero VARCHAR(128))
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS ObjectName
FROM sys.objects o
WHERE o.name = 'MySuperTable';

Again, no dice!

Msg 229, Level 14, State 5, Line 157
The SELECT permission was denied on the object ‘objects’, database ‘mssqlsystemresource’, schema ‘sys’.

With this constant blocker of being denied cropping up, let’s take a look at relevant permissions related to the database roles to which I added the superuser principal. To evaluate those permissions, I am going to use the following script that relies on the results of a system stored procedure called sp_dbfixedrolepermission. (Word of advice, this procedure is deprecated and I will be writing more on that in a future article.)

CREATE TABLE #role_permission
(
DBFixedRole VARCHAR(128)
, Permission VARCHAR(128)
)
INSERT INTO #role_permission
( DBFixedRole, Permission )
EXECUTE sp_dbfixedrolepermission
 --'db_denydatareader'
GO
DECLARE @principalname VARCHAR(128) = 'superuser';
SELECT pout.PrincipalName
, r.DBFixedRole
, r.Permission AS PermissionDesc
, SUBSTRING(REPLACE(r.Permission, 'No ', ''), 1, 6) AS PermissionName
, CASEWHEN LEFT(r.Permission, 3) = 'No ' THEN 'DENY'
ELSE 'GRANT'
END AS PermissionState
, ROW_NUMBER() OVER ( PARTITION BY REPLACE(r.Permission, 'No ', '') ORDER BY REVERSE(r.Permission) ASC ) AS RowNum
FROM #role_permission r
CROSS APPLY ( SELECT rm.name AS PrincipalName
, p.name AS rolename
FROM ( SELECT prmi.principal_id
, prmi.name
, rmi.member_principal_id
FROM sys.database_role_members rmi
INNER JOIN sys.database_principals prmi
ON rmi.role_principal_id = prmi.principal_id
) p
INNER JOIN ( SELECT name
, dp.principal_id
FROM sys.database_principals dp
WHERE dp.name = @principalname
) rm
ON rm.principal_id = p.member_principal_id
) pout
WHERE r.DBFixedRole IN ( 'db_owner', 'db_datareader', 'db_datawriter',
'db_denydatareader', 'db_denydatawriter' )
AND ( r.Permission LIKE '%select%'
OR r.Permission LIKE '%update %'
OR r.Permission LIKE '%insert%'
OR r.Permission LIKE '%delete%'
)
AND pout.rolename = r.DBFixedRole
ORDER BY REPLACE(r.Permission, 'No ', '')
, RowNum;
DROP TABLE #role_permission;
GO

Looking at the results of that script, one can start to see why we are having so many problems executing a select.

In this result set, one can see that by virtue of the db_owner role and the db_datareader and db_datawriter roles, we have permissions that are being GRANTed. Immediately after that, we hit the DENY. This behavior is to be expected. A DENY permission will override a GRANT except when the principal is a member of the sysadmin server role.

Seeing this in action, the next logical step is to remove the role membership from the two “deny” roles (in this scenario where all are checked) for starters. Once done, go ahead and re-run these tests, you will see that things will work differently. While you are at it, take it a step further and rerun the tests after removing superuser from the db_owner role. Think about it, do you really need to have db_owner along with all of the “grant” related roles given that db_owner gives all of those same roles?

One last bit of homework is for you to run the tests another time from the point of when the database principal was created. Only this last batch of tests, impersonate the database principal instead of the server principal. You can do that by doing the following:

EXECUTE AS USER = 'superuser'

Instead of using the following impersonation statement:

EXECUTE AS LOGIN = 'superuser'

You may be interested by the results.

Recap

I have taken you on a journey through three common mis-configurations with principals and permissions. It really is quite unnecessary to add a principal to the sysadmin fixed server role as well as every fixed database role. Even if the principal is not a member of the sysadmin role, it is foolish to add it to every database role. There just is no good logic in setting up permissions in that manner.

Take a moment to focus and you can say EZ PZ permission squeezee.