x

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

more ▼

asked Aug 29, 2011 at 01:22 PM in Default

avatar image

basit 1
509 57 65 91

Which version of SQL are you running?

Aug 29, 2011 at 01:32 PM SirSQL

SQL Server 2008 version

Aug 29, 2011 at 01:37 PM basit 1
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Aug 29, 2011 at 01:39 PM

avatar image

WilliamD
26.2k 18 37 48

Thanks William.

Aug 29, 2011 at 01:44 PM basit 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2198
x215
x26

asked: Aug 29, 2011 at 01:22 PM

Seen: 1211 times

Last Updated: Aug 29, 2011 at 01:41 PM

Copyright 2018 Redgate Software. Privacy Policy