Is it Safe? Is it Secure?

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).

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.

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:

Additionally, we can use the following system view to find the individual permissions:

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.

Advertisements
This entry was posted in DBA, Security and tagged , . Bookmark the permalink.

3 Responses to Is it Safe? Is it Secure?

  1. Pingback: DBA Basics – A Review for the New Year | SQL Bad Boy – Michael Lowery

  2. Pingback: How SQL Server Can Make Things More Secure … Use Encryption! | SQL Bad Boy – Michael Lowery

  3. Pingback: Security Cleanup … Identifying Invalid Logins and Orphaned Users | SQL Bad Boy – Michael Lowery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s