New Security Catalog Views in SQL Server 2005/2008
January 9th 2009 11:46
New Security Catalog Views in SQL Server 2005/2008
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_principa l_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;
Schema User permission_name state_desc
HumanResources HR_Employees SELECT GRANT
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
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.
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_principa l_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;
Schema User permission_name state_desc
HumanResources HR_Employees SELECT GRANT
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
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.
| 29 |
| Vote |
Add To: 
