Data Management

Reviewing SQL Server Permissions

SQL Server consultant Tim Chapman looks at the importance of database permissions and how you can use internal SQL Server system views to easily which users have access on your system.

  Permissions on data are one of the most critical aspects of database administration.  If you're too strict as a database administrator then your users will not be able to do their jobs.  If you're not lenient, then data can be compromised or even leaked.  It is a very fine balance to control.  The ability to determine these permissions on your database systems is absolutely paramount.

Who has access to my SQL Server?

First things first, you need to know which users are able to login into your SQL Server instance.  Logins come in two flavors; Windows authentication and SQL Server Logins.  Windows logins are tied to Windows accounts while SQL Server logins are housed in SQL Server internally.   Whether the login is Windows based or is an internal SQL account, you can access login information by querying internal SQL Server views. To find the login information, the sys.server_principals system view can be used.  The following script queries this view and returns login information along with the type of associated login.

SELECT name, type_desc, is_disabled

FROM sys.server_principals

To test this query, run the following script followed by the script above.  The new login TestLogin should appear in the result-set.

CREATE LOGIN TestLogin WITH Password = 'asdevex33', CHECK_POLICY = OFF

Who has access to my Databases?

Once a login is able to gain entry into the server, they then need access to databases.  Before a login is able to access a database, a user must be mapped to that login inside the database.  The following script queries the sys.database_principals system view, which holds user related information for the current database.  Note that this information will likely differ for each database you run it in.  Users are database-level, so different users will have different access in different databases.

SELECT

UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, LoginType = sp.type_desc

FROM sys.database_principals dp

JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id

To test the above view, run the following script followed by the script immediately above.  The new user TestUser (which is now mapped to the login TestLogin) should appear in the result-set.

CREATE USER TestUser FOR LOGIN TestLogin

Server Roles

Now that I have covered server logins and database users, I need to cover the different server and database roles on the system.  A login can be a member of a server role, which gives the login elevated permissions for the SQL Server instance.  The following query can be used to view which logins are tied to which server roles.

select p.name, p.type_desc, pp.name, pp.type_desc

from  sys.server_role_members roles

join sys.server_principals p on roles.member_principal_id = p.principal_id

join sys.server_principals pp on roles.role_principal_id = pp.principal_id

The following script adds the TestLogin I created above to the dbcreator server role. Once this script is ran, rerun the immediate script above.  The new login role will be included in the result-set.

EXECUTE sp_addsrvrolemember

@loginame = 'TestLogin',

@rolename = 'dbcreator'

Database Roles

The previous query illustrated which users had specific permissions inside of your database.  However, when you're a member of a database role, you're given permissions that are not contained in the sys.database_permissions view, but are absolutely vital for knowing which users have permissions inside your database.  You can use the following query to determine which users are assigned to database roles.

SELECT

p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

The following script adds the TestUser to the db_datareader database role.  Once this script has been executed, run the previous script to see the new entry in the sys.database_role_members system view.

 

EXECUTE sp_addrolemember

@rolename = 'db_datareader',

@membername = 'TestUser'

What can these users do?

The following query uses the sys.database_permissions system view to indicate which users had specific permissions inside the current database.

SELECT

      dp.class_desc, dp.permission_name, dp.state_desc,

ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name

FROM sys.database_permissions dp

JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id

JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

Conclusion

Today I looked at some system views included in SQL Server 2005 and SQL Server 2008 which can be used to view permissions on your SQL Server instance.  The more you know about the permissions on your SQL Server system, the more prepared you'll be if problems arise.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

5 comments
ckmutunga
ckmutunga

It is exactly what I was looking for.

JeffNguyen
JeffNguyen

I think for the part "Who has access to my Databases?", the SQL should be. Please correct me if I'm wrong SELECT UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, LoginType = sp.type_desc FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid=sp.sid

Malkie
Malkie

"Permissions on data are one of the most critical aspects of database administration. If you???re too strict as a database administrator then your users will not be able to do their jobs. If you???re not lenient, then data can be compromised or even leaked. It is a very fine balance to control. The ability to determine these permissions on your database systems is absolutely paramount." "If you???re not lenient, then data can be compromised or even leaked." should be "If you???re too lenient, then data can be compromised or even leaked."

Prague
Prague

The Server level queries requires sa-type rights, or you won't see very much information. The Database level queries require dbo-type rights, or you won't see much information. If you have these rights, however, these are very useful queries.