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_disabled
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
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’
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