On our previous article we briefly touched on database roles and what they are, but more importantly, we started to talk about database principals. A database principal is essentially any entity or object that can request SQL Server resources. So what entities are grouped under the principal ‘moniker’? Anythig from database roles downright to database users.
For instance, if we take a look at the list of principals on a [master] database, we might see a listing like this. And if we look under [name] we see that the first value corresponds to [public]; [public] is a database_level role -there is also a certificate mapped user. But why bag roles & users together, if a user can be a member of one or more roles? Because resources can be controlled at database user and role levels alike, both offering varying grades of granularity.
- sys.database_principals
The column definitions offered on the previous article should be rather self explanatory now that you can see actual data. Go ahead, query against one of your database’s sys.database_principals admin view and then compare it to what you see through SQL Server Management Studio for the same database.
Now, the next step in our quest is to figure out what kind of memberships exist for our principal / users -which groups do users belong to? Pretty easy! There is obviously another management view which exposes that information (think of it this way from here on: anything that’s done through SSMS, can be replicated by transact-sql means). In our case, principal-users and role relations are exposed through: sys.database_role_members.
1 | SELECT * FROM sys.database_role_members |
This view will expose the principal_id of the role matched against the principal_id of a user or other entity belonging to a role. And we will see how to exploit this very easily on the next article -I am tired & cranky right now.
|
|
|
|
|
 |
Related posts: