Reviewing SQL Server Permissions

Reviewing SQL Server Permissions

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_disabledFROM sys.server_principalsTo 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 = OFFWho 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.

SELECTUserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, LoginType = sp.type_descFROM sys.database_principals dpJOIN sys.server_principals sp ON dp.principal_id = sp.principal_idTo 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_descfrom sys.server_role_members rolesjoin sys.server_principals p on roles.member_principal_id = p.principal_idjoin sys.server_principals pp on roles.role_principal_id = pp.principal_idThe 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.

SELECTp.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_roleFROM sys.database_role_members rolesJOIN sys.database_principals p ON roles.member_principal_id = p.principal_idJOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_idThe 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.nameFROM sys.database_permissions dpJOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_idJOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id