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
 General SQL Server Forums
 New to SQL Server Programming
 Script to show permissions

Author  Topic 

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-14 : 09:19:34
Hey...don't remeber if I ever saw a script that shows who has permissions and to what....do we have one???

I have a script that will perform/ generate the grants

But I wonder what would happen if you grant to something that already has been granted??? I'm thinking that wouldn't be a good idea



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



X002548
Not Just a Number

15586 Posts

Posted - 2010-09-14 : 09:47:46
aha


SELECT
dp.class_desc, dp.permission_name, dp.state_desc,ObjectName = OBJECT_NAME(major_id)
, GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee
on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor
on dp.grantor_principal_id = grantor.principal_id



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-14 : 10:01:21
does anybody see a problem with this?


Hey

run this

SELECT
dp.class_desc, dp.permission_name, dp.state_desc,ObjectName = OBJECT_NAME(major_id)
, GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grantee
ON dp.grantee_principal_id = grantee.principal_id
INNER JOIN sys.database_principals grantor
ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.name <> 'public'
ORDER BY OBJECT_NAME(major_id)

Then this

SELECT 'GRANT INSERT, SELECT, UPDATE, DELETE ON ' + TABLE_NAME + ' TO Diversity_User99_Role'
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME NOT IN (
SELECT DISTINCT grantee.name
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grantee
ON dp.grantee_principal_id = grantee.principal_id
INNER JOIN sys.database_principals grantor
ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.name <> 'public'
)
ORDER BY TABLE_NAME




I'm seeing tables that already have a grant...I'm confused


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
   

- Advertisement -