Script permissions on all databases

Question

Post reply

Script permissions on all databases

mobasha

SSCertifiable

Points: 5334
More actions
March 9, 2008 at 5:39 pm

#183879

Comments posted to this topic are about the item Script permissions on all databases
..>>..
MobashA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

William Soranno SSCommitted Points: 1713 More actions · Answer 1

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

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 2

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 --

William Soranno SSCommitted Points: 1713 More actions · Answer 3

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

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 4

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 --

Christoph Muthmann Ten Centuries Points: 1164 More actions · Answer 5

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

Have a nice day,Christoph

FreeHansje SSChampion Points: 11751 More actions · Answer 6

Still get truncation errors despite impementing suggestions made here. Not for me.

Greetz,
Hans Brouwer