Thursday, November 11, 2010

List of users and their permissions

Yesterday I wanted to know which users have what permissions to a database. So I started querying the two tables related to it.
select * from sys.database_permissionsselect * from sys.database_principals
I came up with the following script to suit my needs.

select USER_NAME(perm.grantee_principal_id) AS user_name, princ.principal_id, princ.type_desc AS principal_type_desc,
OBJECT_NAME(perm.major_id) AS object_name,
perm.permission_name, perm.state_desc AS permission_state_desc
from sys.database_permissions perm
inner JOIN sys.database_principals princ
on perm.grantee_principal_id = princ.principal_id

There may be numerous other ways to do this.

No comments:

My first experience with SQL Server Database Partitioning

At my work I have inherited a database where the partitioning was not done correctly.  This database holds millions and millions of records...