Script permissions on all databases

  • Comments posted to this topic are about the item Script permissions on all databases

    ..>>..

    MobashA

  • 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

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

  • 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

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

  • 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

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

    Greetz,
    Hans Brouwer

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

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