Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Script DB Level Permissions Expand / Collapse
Author
Message
Posted Tuesday, November 23, 2010 12:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 968, Visits: 966
Comments posted to this topic are about the item Script DB Level Permissions
Post #1024876
Posted Tuesday, November 23, 2010 6:50 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:26 AM
Points: 76, Visits: 426
I like the script, it will be useful.
I have added the following to your code:
DECLARE 
@sql VARCHAR(2048)
,@sort INT

DECLARE tmp CURSOR FOR

then your code goes here.

OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
FETCH NEXT FROM tmp INTO @sql, @sort
END

CLOSE tmp
DEALLOCATE tmp

The result is now text that can be copied into another query window and run or saved to a file.

Thanks for sharing your code.
Bill


Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Post #1025122
Posted Tuesday, November 23, 2010 7:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 968, Visits: 966
Thanks for the feedback. That's a good update to it so that it sorts without displaying the sort counter. I knew it could benefit from some updates.

Thanks!

Steve
Post #1025181
Posted Wednesday, November 24, 2010 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 10:08 AM
Points: 1, Visits: 277
Currently it captures stored procedure execute permissions, what needs modified to capture schema execute permissions?

Thank You
Post #1025979
Posted Wednesday, November 24, 2010 9:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 968, Visits: 966
I believe this would get you the schema permissions you are looking for. I'll have to update my script to include that as well.

select state_desc+ ' ' + permission_name + ' TO ' + class_desc + '::' + QUOTENAME(SCHEMA_NAME(grantee_principal_id)), perm.state
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_i
WHERE class_desc = 'SCHEMA' --class = 3

I did some minimal testing with that code and it seems to work as you desire.

I'm sure there will be other facets missing, so please let me know if you see anything else missing that would be a benefit to the community.

Thanks,
Steve
Post #1026009
Posted Tuesday, November 30, 2010 9:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:57 PM
Points: 21, Visits: 304
Thanks for the Script. It is very useful. While scripting out the object level permissions, i think, the below line that has been highlighted (Underlined) should have been "QUOTENAME(SCHEMA_NAME(obj.schema_id))". Please correct me if i m wrong


/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm

Post #1028378
Posted Tuesday, November 30, 2010 9:49 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 968, Visits: 966
karthik.catchme (11/30/2010)
Thanks for the Script. It is very useful. While scripting out the object level permissions, i think, the below line that has been highlighted (Underlined) should have been "QUOTENAME(SCHEMA_NAME(obj.schema_id))". Please correct me if i m wrong


/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm



Good catch, not sure how I missed that.

I'll get that fixed and will submit a v2 of this script that includes this fix as well as the enhancement to have it "PRINT" the command output instead of in a two-column grid, and will also include schema level DB permissions per the request of a previous poster.

Thanks for checking it out.

Steve
Post #1028381
Posted Wednesday, February 2, 2011 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612
Thanks for the new v2 version. It looks great! This will be very helpful.


Post #1057530
Posted Thursday, May 5, 2011 1:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 6, 2011 7:26 AM
Points: 2, Visits: 5
Are there any version to run it in SQL Server 2000? Or what needs to be changed to be able to run in SQL Server 2000?
Post #1103676
Posted Thursday, May 5, 2011 4:18 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 968, Visits: 966
Unfortunately, no. I never made one for 2000 and we have so few DB's still in 2000 that it isn't worthwhile to me. There may be someone else on the thread who might have something like that.

Sorry,
Steve
Post #1104255
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse