I was needing to get an idea of the permissions that had been granted in a couple of databases the other day, and being the GUI novice I am, couldn't find what I wanted easy enough (if it even is in there.) So, I hunted around in the system views to see what I could find. There are two views of major interest for permissions: sys.database_permissions and sys.server_permissions. So I formulated the following query:
select database_permissions.permission_name,
coalesce(objects.type_desc,database_permissions.class_desc)
+ case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
else '' end as object_type,
case database_permissions.class_desc
when 'SCHEMA' then schema_name(major_id)
when 'OBJECT_OR_COLUMN' then
case when minor_id = 0 then object_name(major_id)
else (select object_name(object_id) + '.'+ name
from sys.columns
where object_id = database_permissions.major_id
and column_id = database_permissions.minor_id) end
else 'other' end as object_name,
database_principals.name as database_principal,
database_permissions.state_desc as grant_state
from sys.database_permissions
join sys.database_principals
on database_permissions.grantee_principal_id = database_principals.principal_id
left join sys.objects --left because it is possible that it is a schema
on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name
The columns returned are:
- permission_name - SELECT, INSERT, UPDATE, DELETE (only dealing with table rights in this query)
- object_type - The type of objects, view, table, function. Based on values from sys.database_permissions
- object_name - the table, view or function, plus the column if it is a column view
- database_principal - the user who has the rights granted or denied
- grant_state - if the right has been given (GRANT) or explicitly disallowed (DENY)
As a demonstration, consider the following batch (I ran this in my tempdb):
create user bob without login
create user fred without login
create user silvio without login
go
create table test
(
testId int,
value varchar(10)
)
go
grant select on test to fred
grant insert, update, delete on test to fred
go
grant select on test to bob
deny select on test(value) to bob
go
grant select on schema::dbo to silvio
go
create function test$select()
returns table
as
return (select *
from test)
go
grant select on test$select to public
go
create view testNoNullValue
as
select *
from test
where value is not null
go
grant select on testNoNullValue to fred,bob
go
Run the query and you should see these results, showing the schema, the tables, the view, the function, and who had rights granted:
permission_name object_type object_name database_principal grant_state
--------------- -------------------------------- ------------------ ------------------ ------------
SELECT SCHEMA dbo silvio GRANT
SELECT USER_TABLE test bob GRANT
DELETE USER_TABLE test fred GRANT
INSERT USER_TABLE test fred GRANT
SELECT USER_TABLE test fred GRANT
UPDATE USER_TABLE test fred GRANT
SELECT SQL_INLINE_TABLE_VALUED_FUNCTION test$select public GRANT
SELECT USER_TABLE-COLUMN test.value bob DENY
SELECT VIEW testNoNullValue bob GRANT
SELECT VIEW testNoNullValue fred GRANT
Coming up soon, a version of the query for execute rights.
Crossposted to: http://drsql.spaces.live.com