New Security Catalog Views in SQL Server 2005 and SQL 2008

By:   |   Updated: 2008-12-24   |   Comments (2)   |   Related: More > Security

Problem
In SQL Server 2000 and below, I relied on sp_helprotect, sp_helprolemember, and sp_helpsrvrolemember to be able to report on all the permissions a given login or user had. However, I'm now supporting SQL Server 2005/2008 and I've read how sp_helprotect is deprecated and doesn't report on all the permissions. What do I need to use?

Solution
SQL Server 2005 introduced the concept of securables, which are anything within SQL Server which you can assign permissions against. This includes the SQL Server itself. Microsoft did this to provide more granular access in order to customize security to what you need. But with more flexibility comes more complexity. As a result, you've got to look in a few more places to understand the permissions a given login or database user has.

The use of stored procedures for roles such as sp_helprolemember and sp_helpsrvrolemember is still necessary because some permissions are assigned directly to the roles. Some roles, like db_datareader and db_datawriter, have implicit rights that will need to be taken into account. Those should stay a staple of your toolset when trying to determine security within SQL Server. The reason sp_helprotect is no longer viable at the database level is because it is included for backward compatibility and therefore only reports on those securables that were present in SQL Server 2000, such as tables, views, functions, and the like. Newer securables, such as schemas and databases, weren't in SQL Server 2000 and as a result, sp_helprotect won't report permissions on them because it doesn't report against any securable that wasn't present in SQL Server 2000.

Beyond those stored procedures, you'll also want to use the security catalog views sys.server_permissions and sys.database_permissions. There are catalog views for role membership, such as sys.database_role_members and sys.server_role_members, but these are "join tables" used to match up users/logins to the roles they belong to. They are used by the sp_helprolemember and sp_helpsrvrolemember stored procedures to report back role membership.

The reason you'll need to query the additional security catalog views is that they are your only source for permissions assigned to the new securables. For instance, members of the sysadmin fixed server role implicitly have the CONTROL SERVER right. This right grants a multitude of other rights, as seen by the GRANT Server Permissions topic in Books Online. While it is understood that members of the sysadmin server role have these rights, it is also possible for the CONTROL SERVER right to be assigned directly to a login. The following query will show any logins which have the CONTROL SERVER right on the server. The sysadmin fixed server role does not show up because it implicitly has this permission.

SELECT SUSER_NAME(grantee_principal_id[Login]
FROM sys.server_permissions
WHERE type 'CL'
   
AND state 'G'

At the database level, permissions granted against the database securable applies to all objects of that type within the database. The same is true for a schema. For instance, if a role is granted SELECT permissions to a schema, that role can issue SELECT queries against all tables and views contained in that schema. To show this, execute the following query on a SQL Server where you have the AdventureWorks2008 database installed (use AdventureWorks for SQL Server 2005):

CREATE ROLE HR_Employees;
GO 

GRANT SELECT ON SCHEMA::HumanResources TO HR_Employees;
GO 

This sets up the role HR_Employees with select rights against the HumanResources schema. To see these rights, you can execute the following query (for what type of object a given permission refers to, look in Books Online under the topic sys.database_permissions):

SELECT  
  
s.name [Schema]
  
USER_NAME(dp.grantee_principal_id[User]
  
permission_name
  
state_desc
FROM sys.database_permissions dp
JOIN sys.schemas s ON dp.major_id s.schema_id 
WHERE class 3

You'll see that such a permission is not returned by sp_helprotect by executing the following query:

EXEC sp_helprotect @username 'HR_Employees'

You should get the following error:

Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 291
There are no matching rows on which to report.

Therefore, through the use of sys.server_permissions and sys.database_permissions, you'll be able to see all the permissions within your databases and on your SQL Servers.

Next Steps

  • Be aware that some of the permissions are now stored in other system tables and older commands such as sp_helprotect does not always return the data that you think you may get.
  • Take a look at these other security related tips.


Last Updated: 2008-12-24


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, May 27, 2011 - 10:19:00 AM - K. Brian Kelley Back To Top

You can use both. While the old views are going to be phased out, they are still present. If you are auditing permissions, however, be aware that the older views will only return information that was present in SQL Server 2000.


Friday, May 27, 2011 - 10:02:59 AM - Lelo121 Back To Top

Question:

Let's say that my database version is SQL Server 2005 after being upgraded from SQL Server 2000. Will I need to use the backward compatibility stored procedures, the new views, or both?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools