Everyone to some extent is concerned about safety or security. We may be concerned about our physical safety and avoid dangerous areas. We may be concerned about the safety of our belongings and that may be why we lock the doors on our house and car. We could be concerned when we travel and take appropriate precautions. We might be concerned about what we take into our bodies, such as the food we eat and what we drink. Or we might be rightfully concerned about electronic security and not being a victim of identity theft.
Since thinking about safety and security is simply a part of daily life, we should also think about it when it comes to our work. And as a DBA, we have to be concerned with the safety and security of our databases. If this is not one of our concerns, there could be very damaging consequences to our company and our employment there …
Ideally, you would have a security policy that dictates what types of permissions groups, users, and even applications can have. However, many times we may inherent a SQL Server or even start a new job where that was not the case. Well, what do we do in those situations? In this post, I am going to show how to determine what security exists on the SQL Server at the server and database levels.
Server Level Rights
There are a few ways that we can find out who can login to a SQL Server:
- sp_helplogins (http://msdn.microsoft.com/en-us/library/ms190304.aspx) – This security stored procedure provides information about logins and the users associated with them in each database. There are two record sets returned from this stored procedure. The first focuses on the logins and the second shows the connection between the logins and the users in the databases.
- sys.server_principals (http://msdn.microsoft.com/en-us/library/ms188786.aspx) – This system view contains information on every server level principal (i.e. logins and server roles). The nice thing about using a system view is the ability to easily use T-SQL and code scripts/processes that do not always have to be dumped to a table to use.
- xp_logininfo (http://msdn.microsoft.com/en-us/library/ms190369.aspx) – This extended stored procedure focuses on only Windows users and groups, so SQL logins are not accounted for in the results. Additionally, there is a “members” option that returns the next level of users in a group. However, if on your domain there are multiple nested groups, this will not return the next level groups, only users.
* NOTE: There are two authentication modes (http://msdn.microsoft.com/en-us/library/ms144284.aspx) that SQL Server can use (i.e. Windows Authentication vs. SQL Server Authentication). The preferred method is to use Windows authentication.
Once you know who can login to a SQL Server, the next thing you want to determine is if they are assigned to any server-level roles (http://msdn.microsoft.com/en-us/library/ms188659.aspx).
- sp_helpsrvrolemember (http://msdn.microsoft.com/en-us/library/ms188772.aspx) – This stored procedure simply returns the members of a SQL Server fixed server role.
- sys.server_role_members (http://msdn.microsoft.com/en-us/library/ms190331.aspx) – This system view is an intersection table that joins back to the sys.server_principals table for both the role and members. It will display fixed and user-defined server roles.
SELECT rm.role_principal_id, role.name AS role_name, rm.member_principal_id, mem.name AS member_name FROM sys.server_role_members rm INNER JOIN sys.server_principals role ON rm.role_principal_id = role.principal_id INNER JOIN sys.server_principals mem ON rm.member_principal_id = mem.principal_id
Due to the fact that someone could create a user-defined server role or assign any login (i.e. server principal) a server level right, you may need to investigate the individual permissions assigned to them at the server level.
- sys.server_permissions (http://msdn.microsoft.com/en-us/library/ms186260.aspx) – This system view contains a row for each permission assigned at the server level.
SELECT pe.class_desc, pe.type, pe.permission_name, pe.state_desc, pr.name FROM sys.server_permissions pe INNER JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id
Database Level Rights
The best way to find out who has access to a database would be to use the following:
- sys.database_principals (http://msdn.microsoft.com/en-us/library/ms187328.aspx) – This system view contains information on every database level principal (i.e. users and database roles).
Additionally, we can use the following system view to find the individual permissions:
- sys.database_permissions (http://msdn.microsoft.com/en-us/library/ms188367.aspx) – This system view contains a row for each permission assigned at the database level.
SELECT pe.class_desc, pe.type, pe.permission_name, pe.state_desc, pr.name, pr.type_desc FROM sys.database_permissions pe INNER JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
* NOTE: There were some stored procedures (sp_helpuser, sp_helprotect) that also returned this type of information, but they are now deprecated and will be removed in a future release.
As you can see, SQL Server makes a DBA’s life much easier by providing many objects that can be used to keep an eye on security.