question

basit 1 avatar image
basit 1 asked

How can we get the Detail of Object and there access in the User Define Role?

Hi Team, How can we get the Detailed list of object and the access in user define role. We have one role in our Database having 1000s of object . Is there any Query which fetch all the object and there access in the role. Thanks Basit Khan
sql-server-2008securityroles
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Which version of SQL are you running?
1 Like 1 ·
SQL Server 2008 version
0 Likes 0 ·

1 Answer

·
WilliamD avatar image
WilliamD answered
This should work (only tested on Denali CTP3): DECLARE @RoleName varchar(255) set @Rolename = 'public' -- change to fit your role name select dp.name, object_name(perms.major_id), perms.permission_name from sys.database_principals dp inner join sys.database_permissions perms on perms.grantee_principal_id = dp.principal_id where dp.name = @Rolename
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks William.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.