Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Auditing SQL Server 2012 Server Roles

MSSQLTips author K. Brian Kelley By:   |   Read Comments (1)   |   Related Tips: More > Security
Problem

My organization is looking at SQL Server 2012 and I know that the ability to create roles at the server level is a new feature. Since this is new and impacts security, how do I handle them and how do I audit them?

Solution

Handling server roles in SQL Server 2012 doesn't differ much from handling permissions for logins in SQL Server 2005, 2008, and 2008R2. The catch is we have a level of abstraction because of the role.

Let's start by creating a test role to demonstrate our audit scripts with:

-- Creating a new server role
CREATE SERVER ROLE Example_Server_Role;
GO 

In SQL Server 2012 we still use the sys.server_principals catalog view to see the roles. There is a new column, is_fixed_role, that tells us whether the role is a traditional fixed server role or a user created one. The one exception is public, which is not marked as a fixed role for some reason.

In SQL Server 2012 we still use the sys.server_principals catalog view to see the roles

Knowing this, we can query to see what roles exist, when they were created, and whether or not they were user created.

-- Seeing which server roles are "fixed"
SELECT principal_id, [name], create_date, is_fixed_role 
FROM sys.server_principals
WHERE [type] = 'R';
GO  

If we just want to filter to the user created server roles, the following query gets that information:

-- See created server roles only
SELECT principal_id, [name]
FROM sys.server_principals
WHERE [type] = 'R'
  AND NOT [name] = 'public'; 

Now let's add some permissions to our example server role so we can see them in our audit queries:

-- Grant example permissions
GRANT ALTER ANY SERVER ROLE TO Example_Server_Role;
GRANT IMPERSONATE ON LOGIN::sa TO Example_Server_Role; 

Now we can query and see permissions. Note that in the case of IMPERSONATE, we'll need to determine who exactly the role is allowed to impersonate, so there's some additional work that's required.

-- See permissions assigned to server roles
SELECT prin.name, perm.state_desc, perm.class_desc, 
       CASE perm.permission_name 
         WHEN 'IMPERSONATE' THEN 'IMPERSONATE ON [' + imp.name + ']'
         ELSE perm.permission_name END COLLATE SQL_Latin1_General_CP1_CI_AS AS [permission]
FROM sys.server_permissions perm
  JOIN sys.server_principals prin
    ON perm.grantee_principal_id = prin.principal_id
  LEFT JOIN sys.server_principals imp
    ON perm.major_id = imp.principal_id
WHERE prin.[type] = 'R'; 

This clearly reveals that Example_Server_Role has permissions we definitely want to be aware of.

This clearly reveals that Example_Server_Role has permissions we definitely want to be aware of

Finally, we'll want to see who is a member of each role:

-- See who the members for each server role are
SELECT roles.[name] AS 'Role', prin.[name] AS 'Member'
FROM sys.server_role_members mem
  JOIN sys.server_principals roles
    ON mem.role_principal_id = roles.principal_id
  JOIN sys.server_principals prin
    ON mem.member_principal_id = prin.principal_id
ORDER BY roles.[name], prin.[name];
Next Steps


Last Update: 11/14/2012


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, November 14, 2012 - 3:39:08 PM - JC Read The Tip

You might find this script (procedure) helpful

I have used it on sql server 2005

http://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.