SQL 2005 - Permissions

[Bell1991]

How can i find all the permssions a user has (i.e., what sproc's the can execute) without going one by one in t2005?

 

[mrdenny]

This will do the trick for you.

select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
    sys.database_permissions.permission_name, 
    sys.database_permissions.state permission_state,
    sys.database_permissions.state_desc,
    state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = 
    sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = 
    sys.database_principals.principal_id
order by 1, 2, 3, 5

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kittyyo]

You might also look at sp_helprotect

http://msdn2.microsoft.com/en-us/library/ms190310.aspx

Anne