August 22, 2014 at 6:22 pm
Hi friends, can you please tell me whether this script is correct or need any changes. Please suggest.
This script is used to exact users and their permissions on the databases
SET NOCOUNT ON
SELECT name FROM master..sysdatabases
where name in ('database1','database2')
Declare C CURSOR FOR
SELECT
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE dp.state_desc
END
+ ' ' + dp.permission_name + ' ON ' +
CASE dp.class
WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'
WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']'
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']'
END
+ ' TO [' + USER_NAME(grantee_principal_id) + ']' +
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END
COLLATE DATABASE_DEFAULT
FROM sys.database_permissions dp
LEFT JOIN sys.all_objects o
ON dp.major_id = o.OBJECT_ID
WHERE dp.class < 4
AND major_id >= 0
AND grantee_principal_id <> 1;
Regards,
SAM
***Share your knowledge.It’s a way to achieve immortality----Dalai Lama***
August 22, 2014 at 6:23 pm
Have you tried running the script in a sandbox database?
August 22, 2014 at 11:37 pm
Looks to me that it does what it says on the tin.
😎
Inspecting the filter, we find:
WHERE dp.class < 4
Listing permissions for
0 = Database
1 = Object or Column
3 = Schema
AND major_id >= 0
Excluding system objecta
AND grantee_principal_id <> 1;
Not the database principal.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply