-- Determines public permissions and applies to all other existing groups before removing from the public group
    -- NB The last part of this task is to run sp_changegroup 'presetAppGrp', 'dbo' as dbo currently resides in the 
    -- public group and will lose its privileges.
    

    -- Drop Temp Tables

    IF OBJECT_ID('tempdb..#Permissions') IS NOT NULL DROP TABLE #Permissions
    IF OBJECT_ID('tempdb..#Groups') IS NOT NULL DROP TABLE #Groups    
    GO
    
    SET NOCOUNT ON
    
    -- Declare Variables
    
    DECLARE @RemovePermissions varchar(50)         
    SELECT  @RemovePermissions = 'public'
            
    IF NOT EXISTS (SELECT * FROM sysusers WHERE name = @RemovePermissions AND suid <= 0)
        BEGIN
            PRINT 'Invalid Group: %1!',@RemovePermissions
            RETURN
        END
            
    -- Create Table with identity
          
    CREATE TABLE #Permissions (    
        RowNumber int IDENTITY,
        ProtectType varchar(50),
        Action varchar(50),
        Object varchar(100),
        UserName varchar(50)  
    )
    
    -- Get Permissions Applicable for Public Group
      
    INSERT INTO #Permissions (ProtectType, Action, Object, UserName)   
       
    SELECT
            CASE p.protecttype
                WHEN 0 THEN 'WITH GRANT OPTION'
                WHEN 1 THEN 'GRANT'
                WHEN 2 THEN 'REVOKE'
                ELSE 'Error'
            END as ProtectType,
            CASE p.action
                WHEN 193 THEN 'SELECT'
                WHEN 197 THEN 'UPDATE'
                WHEN 196 THEN 'DELETE'
                WHEN 195 THEN 'INSERT'
                WHEN 151 THEN 'REFERENCES'
                WHEN 224 THEN 'EXECUTE'
                WHEN 167 THEN 'SET PROXY'
                WHEN 198 THEN 'CREATE TABLE'
                WHEN 203 THEN 'CREATE DATABASE'
                WHEN 205 THEN 'GRANT'
                WHEN 206 THEN 'REVOKE'
                WHEN 207 THEN 'CREATE VIEW'
                WHEN 221 THEN 'CREATE TRIGGER'
                WHEN 222 THEN 'CREATE PROCEDURE'
                WHEN 228 THEN 'DUMP DATABASE'
                WHEN 233 THEN 'CREATE DEFAULT'
                WHEN 235 THEN 'DUMP TRANSACTION'
                WHEN 236 THEN 'CREATE RULE'
                WHEN 282 THEN 'DELETE STATISTICS'
                WHEN 320 THEN 'TRUNCATE TABLE'
                WHEN 326 THEN 'UPDATE STATISTICS' 
                ELSE CAST(p.action as varchar(10))
            END as Action,
            o.name as Object,
            u.name as UserName
    FROM    
            sysprotects p
            JOIN sysusers u on u.uid = p.uid
                AND u.suid <= 0
                AND u.name = @RemovePermissions
            JOIN sysobjects o on o.id = p.id
                AND o.type <> 'S'

    -- Check that all ProtectTypes are Grant
    
    IF EXISTS (SELECT * FROM #Permissions WHERE ProtectType <> 'GRANT')
        BEGIN
            PRINT 'Revoke or non standard grant permissions exist, please confirm what and why'
            RETURN
        END
    ELSE    
        BEGIN
    
         -- Declare variables
         
        DECLARE @Pointer int, @String varchar(1000)
        SET @Pointer = 1   

        -- Revoke permissions from old group
       
        SET @Pointer = 1 

        PRINT 'Beginning revoke of %1! permissions',@RemovePermissions

        WHILE @Pointer <= (SELECT MAX(RowNumber) FROM #Permissions) 

	        BEGIN
                       
                SELECT  @String = 'REVOKE ' + Action + ' ON ' + Object + ' TO ' + @RemovePermissions
                FROM    #Permissions 
                WHERE   RowNumber = @Pointer
                            
                EXEC(@String)

                SET @Pointer = @Pointer + 1    

            END 

        PRINT 'Permissions were removed from %1! group',@RemovePermissions  
        
        -- Show All Permissions from group removed from

        SELECT
                CASE p.protecttype
                    WHEN 0 THEN 'WITH GRANT OPTION'
                    WHEN 1 THEN 'GRANT'
                    WHEN 2 THEN 'REVOKE'
                    ELSE 'Error'
                END as ProtectType,
                CASE p.action
                    WHEN 193 THEN 'SELECT'
                    WHEN 197 THEN 'UPDATE'
                    WHEN 196 THEN 'DELETE'
                    WHEN 195 THEN 'INSERT'
                    WHEN 151 THEN 'REFERENCES'
                    WHEN 224 THEN 'EXECUTE'
                    WHEN 167 THEN 'SET PROXY'
                    WHEN 198 THEN 'CREATE TABLE'
                    WHEN 203 THEN 'CREATE DATABASE'
                    WHEN 205 THEN 'GRANT'
                    WHEN 206 THEN 'REVOKE'
                    WHEN 207 THEN 'CREATE VIEW'
                    WHEN 221 THEN 'CREATE TRIGGER'
                    WHEN 222 THEN 'CREATE PROCEDURE'
                    WHEN 228 THEN 'DUMP DATABASE'
                    WHEN 233 THEN 'CREATE DEFAULT'
                    WHEN 235 THEN 'DUMP TRANSACTION'
                    WHEN 236 THEN 'CREATE RULE'
                    WHEN 282 THEN 'DELETE STATISTICS'
                    WHEN 320 THEN 'TRUNCATE TABLE'
                    WHEN 326 THEN 'UPDATE STATISTICS' 
                    ELSE CAST(p.action as varchar(10))
                END as Action,
                o.name as Object,
                u.name as UserName
        FROM    
                sysprotects p
                JOIN sysusers u on u.uid = p.uid
                    AND u.suid <= 0
                    AND u.name = @RemovePermissions           
                JOIN sysobjects o on o.id = p.id      
        
    END        
    GO

    -- Part 2: Apply permissions to all other existing groups
    
    SET NOCOUNT ON
    
    IF EXISTS (SELECT * FROM #Permissions WHERE ProtectType <> 'GRANT')
        BEGIN
            PRINT 'Revoke or non standard grant permissions exist, please confirm what and why'
            RETURN
        END
    ELSE    
        BEGIN    
        
        -- Get Current Group Structure
     
        SELECT
                name as GroupName  
        INTO    #Groups  
        FROM    
                sysusers 
        WHERE
                suid = -2
                AND environ IS NOT NULL        
    
        -- Declare variables
         
        DECLARE @Pointer int, @String varchar(1000), @Group varchar(50)
            
        -- Apply permissions to other groups
        
        WHILE EXISTS (SELECT * FROM #Groups) 
        
            BEGIN     
            
            SET @Pointer = 1
            SELECT TOP 1 @Group = GroupName FROM #Groups
            
            PRINT 'Begin adding permissions to group %1!',@Group 

	        WHILE @Pointer <= (SELECT MAX(RowNumber) FROM #Permissions) 

	            BEGIN
                           
                    SELECT  @String = ProtectType + ' ' + Action + ' ON ' + Object + ' TO ' + @Group
                    FROM    #Permissions 
                    WHERE   RowNumber = @Pointer          
                                        
                    EXEC(@String)
    
                    SET @Pointer = @Pointer + 1    
            
    	        END 
             
            DELETE FROM #Groups WHERE GroupName = @Group    
            PRINT 'Permissions were transferred to group %1!',@Group
                
            END          
                  
        -- Show All created Permissions
    
        SELECT
                CASE p.protecttype
                    WHEN 0 THEN 'WITH GRANT OPTION'
                    WHEN 1 THEN 'GRANT'
                    WHEN 2 THEN 'REVOKE'
                    ELSE 'Error'
                END as ProtectType,
                CASE p.action
                    WHEN 193 THEN 'SELECT'
                    WHEN 197 THEN 'UPDATE'
                    WHEN 196 THEN 'DELETE'
                    WHEN 195 THEN 'INSERT'
                    WHEN 151 THEN 'REFERENCES'
                    WHEN 224 THEN 'EXECUTE'
                    WHEN 167 THEN 'SET PROXY'
                    WHEN 198 THEN 'CREATE TABLE'
                    WHEN 203 THEN 'CREATE DATABASE'
                    WHEN 205 THEN 'GRANT'
                    WHEN 206 THEN 'REVOKE'
                    WHEN 207 THEN 'CREATE VIEW'
                    WHEN 221 THEN 'CREATE TRIGGER'
                    WHEN 222 THEN 'CREATE PROCEDURE'
                    WHEN 228 THEN 'DUMP DATABASE'
                    WHEN 233 THEN 'CREATE DEFAULT'
                    WHEN 235 THEN 'DUMP TRANSACTION'
                    WHEN 236 THEN 'CREATE RULE'
                    WHEN 282 THEN 'DELETE STATISTICS'
                    WHEN 320 THEN 'TRUNCATE TABLE'
                    WHEN 326 THEN 'UPDATE STATISTICS' 
                    ELSE CAST(p.action as varchar(10))
                END as Action,
                o.name as Object,
                u.name as UserName
        FROM    
                sysprotects p
                JOIN sysusers u on u.uid = p.uid
                    AND u.suid <= 0     
                JOIN sysobjects o on o.id = p.id    
        WHERE
                u.suid = -2
                AND u.environ IS NOT NULL  
            
    END
    GO

    EXEC sp_changegroup 'presetAppGrp','dbo'
    GO