Security - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Security

Author

Topic

ChrisRusty
Starting Member

1 Post

Posted - 2012-06-01 : 11:10:49

Im currently trying to find a good way to verify which users have WITH GRANT permissions on the databases. I have the following script that I can run against the database and it will show me results. Only issue is we have 100s of databases and it would be hard to run against all of them. Is there a way that I could some how incorparate this into a policy or condition and have it checked that way. Im open for suggestions on a good way to manage this.

select
a.*,
b.name
from sys.database_permissions a
inner join sys.database_principals b
on a.grantee_principal_id = b.principal_id
Where state_desc = 'GRANT_WITH_GRANT_OPTION'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-01 : 11:21:13

exec sp_MSforeachdb 'select ''?'' database_name, a.*,b.name
from

.sys.database_permissions a
inner join

.sys.database_principals b
on a.grantee_principal_id = b.principal_id
Where state_desc = ''GRANT_WITH_GRANT_OPTION'' '

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources