December 14, 2008 at 10:56 am
Hi Folks,
How can i write a script to list the DDL Statement Permissions for all Database Users
Thanks,
Reddy
December 15, 2008 at 2:40 am
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 permission_name in ('create','alter','drop')
ORDER BY 1, 2, 3, 5
Apart from the above script output you need to check for users who are members of db_ddladmin database role.
HTH.
MJ
March 4, 2010 at 10:44 am
How can you safely change them?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply