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:
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.
Knowing this, we can query to see what roles exist, when they were created, and whether or not they were user created.
If we just want to filter to the user created server roles, the following query gets that information:
Now let's add some permissions to our example server role so we can see them in our audit queries:
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.
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:
Source Collected from MSSQLTIPS.COM
No comments :
Post a Comment