Querying Database Role Memberships & Members on SQL 2005
A question was raised not long ago on one of the boards I frequently visit, by one member who was trying to generate an report for his management team to audit database level roles & memberships -basically, who could do what and where?
The platform in question was Microsoft SQL Server 2005, and the key requirement was to be able to retrieve the same basic information that can be accessed via SQL Server Management Studio. So I set off to take a peek at the management views available in SQL 2005, and shortly after found what I was looking for.
At the database level, there are 2 basic objects that are extremely useful when it comes to roles & memberships. These are:
- sys.database_role_members - Returns one row for each member of each database role.
- sys.database_principals – Returns a row for each principal in a database.
At this point, I think its worth reviewing some concepts regarding SQL security related objects to make sure we are better prepared to absorb what we will be doing.
What is a Database Role?
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership; however, only members of the db_owner database role can add members to the db_owner fixed database role.
The fixed database roles are the following:
- db_accessadmin
- db_backupoperator
- db_datareader
- db_datawriter
- db_ddladmin
- db_denydatareader
- db_denydatawriter
- db_owner
- db_securityadmin
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
Also, remember that database roles can be created using CREATE ROLE role_name and members assigned and removed from them. This is, for the time being, outside of the scope and purpose of this article.
What are Database Principals?
Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).
A database user is a principal at the database level. Every database user is a member of the public role.
So, now that we have a textbook -literally lifted from BOL- definition of what are database roles & principals, let’s inspect the data we get from our two management views.
Querying sys.database_principals
Let’s run the following snippet on any database, for arguments sake, let’s do so on our [master] database of any database instance we might have handy.
1 | SELECT * FROM sys.database_principals |
Our dataset will contain a listing of the following columns:
- name – data type: sysname
- Name of principal, unique within the database.
- principal_id – data type: int
- ID of principal, unique within the database.
- type – data type: char(1)
- Principal type: S = SQL user U = Windows user G = Windows group A = Application role R = Database role C = User mapped to a certificate K = User mapped to an asymmetric key
- type_desc – data type: nvarchar(60)
- Description of principal type: SQL_USER WINDOWS_USER WINDOWS_GROUP APPLICATION_ROLE DATABASE_ROLE CERTIFICATE_MAPPED_USER ASYMMETRIC_KEY_MAPPED_USER
- default_schema_name – data type: sysname
- Name to be used when SQL name does not specify schema. Null for principals not of type S, U, or A.
- create_date – data type: datetime
- Time at which the principal was created.
- modify_date – data type: datetime
- Time at which the principal was last modified.
- owning_principal_id – data type: int
- ID of the principal that owns this principal. All principals except Database Roles must be owned by dbo.
- sid – data type: varbinary(85)
- SID (Security Identifier) if the principal is defined external to the database (type S, U, and G). Otherwise, NULL.
- is_fixed_role – data type: bit
- If 1, then this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter.
Let’s review the key elements on the next article…
|
|
|
|
|
 |
Related posts: