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

Script permissions on all databases Expand / Collapse
Author
Message
Posted Sunday, March 9, 2008 5:39 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284
Comments posted to this topic are about the item Script permissions on all databases

..>>..

MobashA
Post #466435
Posted Tuesday, May 6, 2008 7:15 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 77, Visits: 436
Nice script.
But I had to increase the size of a2 to 75 to avoid truncation errors on SQL 2005.
Here is an updated script. I changed the names of the fields of table #t for readability.
USE master
go
BEGIN
DECLARE @databasename VARCHAR(30)
DECLARE cur CURSOR
FOR SELECT
name
FROM
sysdatabases
CREATE TABLE #result
(
dbname VARCHAR(30)
,result VARCHAR(300))
OPEN cur
FETCH NEXT FROM cur INTO @databasename
WHILE(@@fetch_status = 0)
BEGIN
CREATE TABLE #t
(
[Owner] VARCHAR(50)
,[Object] VARCHAR(75)
,Grantee VARCHAR(50)
,Grantor VARCHAR(50)
,ProtectType VARCHAR(50)
,[Action] VARCHAR(50)
,[Column] VARCHAR(50))
INSERT INTO
#t
EXEC sp_helprotect @username = NULL
INSERT INTO
#result
SELECT
@databasename
,ProtectType + ' ' + [Action] + ' on [' + [Owner] + '].['
+ [Object] + ']'
+ CASE WHEN (PATINDEX('%All%', [Column]) = 0)
AND ([Column] <> '.') THEN ' (' + [Column] + ')'
ELSE ''
END + ' to [' + Grantee + ']'
FROM
#t
DROP TABLE #t
FETCH NEXT FROM cur INTO @databasename
END
SELECT
*
FROM
#result
ORDER BY
dbname
CLOSE cur
DEALLOCATE cur
DROP TABLE #result
END
go




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 #495590
Posted Tuesday, May 6, 2008 10:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 1,380, Visits: 400
Grasshopper, your version worked fine on my SQL Server 2000 system when I tested it but still failed on my SQL Server 2005 version with truncation errors.

Any ideas of what columns need updating?

-- Mark D Powell --
Post #495791
Posted Tuesday, May 6, 2008 11:40 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 77, Visits: 436
Mark,
You may need to increase the [Object] field or the [Column] field in table #t.
It will depend on the length of the names you use for your objects and columns.



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 #495831
Posted Tuesday, May 6, 2008 2:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 1,380, Visits: 400
I already had object bumped up so I tried column to 75 and still no luck, but when I bumped everything to 75 it worked. Looking at the results I do not see anything beyound 30 except for the name of some MS provided procedures. If I can get some time I will experiment to see which column is the issue.

Thanks
-- Mark D Powell --
Post #495948
Posted Friday, May 16, 2008 12:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:24 AM
Points: 403, Visits: 244
Hi MobashA.
nice script, but not enough information.
I've posted a script on a german website for sqlserver 2005, which you could use to show all users, grants and roles in a database.
http://www.insidesql.org/beitraege/administration/berechtigungen-im-sqlserver-2005-anzeigen
You will find it at the bottom.

I had the idea after reading Jamie Thomson's blog:
http://blogs.conchango.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

Have a nice day,
Christoph
Post #501802
Posted Wednesday, July 8, 2009 8: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: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Still get truncation errors despite impementing suggestions made here. Not for me.


Greetz,
Hans Brouwer
Post #749334
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse