Script DB Level Permissions

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by S. Kusen » Script DB Level Permissions

Add to briefcase

16 posts, Page 1 of 2

1 2 »»

Script DB Level Permissions

Expand / Collapse

Author

Message

Posted Tuesday, November 23, 2010 12:02 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873

Comments posted to this topic are about the item Script DB Level Permissions

Post #1024876

William Soranno

Posted Tuesday, November 23, 2010 6:50 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:42 AM
Points: 65, Visits: 344

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 148

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92

Post #1025122

Posted Tuesday, November 23, 2010 7:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873

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

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 1, Visits: 211

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!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873

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

karthik.catchme

Posted Tuesday, November 30, 2010 9:44 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:45 AM
Points: 12, Visits: 124

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!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873

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 02, 2011 8:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 AM
Points: 1,268, Visits: 1,477

Thanks for the new v2 version. It looks great! This will be very helpful.

Post #1057530

Posted Thursday, May 05, 2011 1:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 06, 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 05, 2011 4:18 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873

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

16 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse