January 6, 2009 at 5:48 pm
Hi Guys,
I have created a database role db_executor with some select,drop or execute permission to that role(I do not remember). Now i want to check that what permission is assigned to that user database role. Can anyone please help me to find out that how can i check that permission????
Thank you very much in advance.
January 6, 2009 at 8:34 pm
Use this:
SELECT C.name 'Schema',
B.name AS Object,
D.name username,
A.type permissions_type,
A.permission_name,
A.state permission_state,
A.state_desc,
state_desc
+ ' ' + permission_name
+ ' ON ['+ C.name
+ '].[' + B.name + '] to ['
+ D.name
+ ']' COLLATE LATIN1_General_CI_AS AS
Generated_Code
FROM sys.database_permissions AS A JOIN sys.objects AS B ON
A.major_id =
B.object_id
JOIN sys.schemas AS C ON B.schema_id =
C.schema_id
JOIN sys.database_principals AS D ON
A.grantee_principal_id = D.principal_id
where D.name='SPECIFY ROLENAME HERE'
ORDER BY 1, 2, 3, 5
HTH,
MJ
January 7, 2009 at 3:53 pm
Hi,
Thanks for ur reply. But that script didn't work. It just returned the column names with blank output. I have created one user defined role called db_executor and i have given execute permission to that role. But if i want to check that permission then how can i check it?
I will be waiting for ur reply. Thanks for ur time.
January 7, 2009 at 3:57 pm
Did you specified the db_executor role name under the where clause:
where D.name='SPECIFY ROLENAME HERE'
MJ
January 7, 2009 at 5:28 pm
I did specify. But no worries now. I got another script. That worked for me. Here is the Script.
select dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply