Monday 20 February 2012

A Script A Day - Day 12 - Granting Role Membership to All Principals


Today's script will grant membership in the db_owner fixed database role in all online read/writable user databases to all principals not in the sysadmin fixed server role.  I have found this script useful when setting up development servers where all developers are given this level of access.  This could however be modified to grant other permissions as required.  Have a look and a play ;)


/*
      -----------------------------------------------------------------
Grant membership in the db_owner fixed database role in all online read/writable user databases to all principals not in the sysadmin fixed server role
      -----------------------------------------------------------------
     
      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.
 
      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
     
      -----------------------------------------------------------------
*/
-- Declare varibles
DECLARE @DatabaseName VARCHAR(255)
DECLARE @PrincipalName VARCHAR(255)
DECLARE @MinDBID INT
DECLARE @MaxDBID INT
DECLARE @MinPrincipalID INT
DECLARE @MaxPrincipalID INT
DECLARE @SQL VARCHAR(255)

-- Drop temporary tables if they exist
IF OBJECT_ID('tempDB.dbo.#Principal') IS NOT NULL
    DROP TABLE #Principal ;
IF OBJECT_ID('tempDB.dbo.#SysAdmin') IS NOT NULL
    DROP TABLE #SysAdmin ;
IF OBJECT_ID('tempDB.dbo.#DBOwner') IS NOT NULL
    DROP TABLE #DBOwner ;   
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
    DROP TABLE #Database ;   
   
-- Create temporary tables
CREATE TABLE #Principal
    (
      ID INT IDENTITY(1, 1),
      PrincipalName VARCHAR(100)
    )
CREATE TABLE #SysAdmin
    (
      ID INT IDENTITY(1, 1),
      PrincipalName VARCHAR(100)
    )
CREATE TABLE #DBOwner
    (
      ID INT IDENTITY(1, 1),
      PrincipalName VARCHAR(100)
    )
CREATE TABLE #Database
    (
      ID INT IDENTITY(1, 1),
      DatabaseName VARCHAR(100)
    )
     
--Insert Online, Read/Writable database names into temporary table
IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                                  'distribution', 'reports', 'reportserver',
                                  'reportservertempdb' )
                    AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
                    AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' )
    BEGIN
        INSERT  INTO #Database ( DatabaseName )
                SELECT  name
                FROM    sys.databases
                WHERE   name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                                      'distribution', 'reports',
                                      'reportserver', 'reportservertempdb' )
                        AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
                        AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
    END
   
-- Get all server principals
IF EXISTS ( SELECT  sp.name
            FROM    sys.server_principals sp
            WHERE   sp.[type] IN ( 'U', 'G' )--Windows User or Group]
                    AND sp.[is_disabled] = 0
                    AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
                                           'NT SERVICE\MSSQLSERVER',
                                           'NT SERVICE\SQLSERVERAGENT' ) )
    BEGIN                                      
        INSERT  INTO #Principal ( PrincipalName )
                SELECT  sp.name
                FROM    sys.server_principals sp
                WHERE   sp.[type] IN ( 'U', 'G' )--Windows User or Group]
                        AND sp.[is_disabled] = 0
                        AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
                                               'NT SERVICE\MSSQLSERVER',
                                               'NT SERVICE\SQLSERVERAGENT' )
    END

-- Get all principals who are members of the sysadmin fixed server role   
IF EXISTS ( SELECT  sp.name
            FROM    sys.server_role_members srm
                    INNER JOIN sys.server_principals sp ON sp.principal_id = srm.member_principal_id
            WHERE   sp.[type] IN ( 'U', 'G' )--Windows User or Group]
                    AND sp.[is_disabled] = 0
                    AND srm.role_principal_id = 3 --sysadmin
                    AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
                                           'NT SERVICE\MSSQLSERVER',
                                           'NT SERVICE\SQLSERVERAGENT' ) )
    BEGIN                                                                                  
        INSERT  INTO #SysAdmin ( PrincipalName )
                SELECT  sp.name
                FROM    sys.server_role_members srm
                        INNER JOIN sys.server_principals sp ON sp.principal_id = srm.member_principal_id
                WHERE   sp.[type] IN ( 'U', 'G' )--Windows User or Group]
                        AND sp.[is_disabled] = 0
                        AND srm.role_principal_id = 3 --sysadmin
                        AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
                                               'NT SERVICE\MSSQLSERVER',
                                               'NT SERVICE\SQLSERVERAGENT' )
    END

-- Only interested in principals which arn't members of sysadmin
DELETE  FROM #Principal
WHERE   PrincipalName IN ( SELECT   PrincipalName
                           FROM     #SysAdmin )
-- Set variables for loop
SELECT  @MinDBID = MIN(ID),
        @MaxDBID = MAX(ID)
FROM    #Database

-- Begin loop
WHILE @MinDBID <= @MaxDBID
    BEGIN
            -- Get DatabaseName
        SELECT  @DatabaseName = DatabaseName
        FROM    #Database
        WHERE   ID = @MinDBID
           
            -- Empty table ready for next database
        TRUNCATE TABLE #DBOwner
       
            -- Get all principals who are members of the db_owner fixed database role   
        IF EXISTS ( SELECT  u.name AS Principal
                    FROM    sys.database_role_members drm
                            INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
                            INNER JOIN sysusers u ON u.uid = drm.member_principal_id
                    WHERE   dp.name = 'db_owner'
                            AND dp.name <> 'dbo'
                            AND u.isntuser = 0 )
            BEGIN
                INSERT  INTO #DBOwner ( PrincipalName )
                        SELECT  u.name AS Principal
                        FROM    sys.database_role_members drm
                                INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
                                INNER JOIN sysusers u ON u.uid = drm.member_principal_id
                        WHERE   dp.name = 'db_owner'
                                AND dp.name <> 'dbo'
                                AND u.isntuser = 0
            END

            -- Set Variables for loop
        SELECT  @MinPrincipalID = MIN(ID),
                @MaxPrincipalID = MAX(ID)
        FROM    #Principal

            -- Begin loop
        WHILE @MinPrincipalID <= @MaxPrincipalID
            BEGIN
                        -- Get DatabaseName
                SELECT  @PrincipalName = PrincipalName
                FROM    #Principal
                WHERE   ID = @MinPrincipalID
               
                -- Build up SQL string
                SET @SQL = 'USE ' + @DatabaseName + ';' + 'EXEC sp_addrolemember N''db_owner'', N''' + @PrincipalName + ''''
               
                -- Grant membership in db_owner fixed database role
                EXEC (@SQL)  
                       
                        -- Get next Principal ID
                SET @MinPrincipalID = @MinPrincipalID + 1
            END
       
        -- Get next Database ID
            SET @MinDBID = @MinDBID + 1
           
    END

Enjoy!

Chris

No comments:

Post a Comment