scripting-permissions-in-sql-server

From:
http://www.gringod.com/2007/02/27/scripting-permissions-in-sql-server


This is just incase this post is pulled down.
Really useful!!!!

--Written By Bradley Morris
--In Query Analyzer be sure to go to
--Query -> Current Connection Options -> Advanced (Tab)
--and set Maximum characters per column
--to a high number, such as 10000, so
--that all the code will be displayed.
--http://www.gringod.com/2007/02/27/scripting-permissions-in-sql-server/
DECLARE  @DatabaseRoleName [SYSNAME]
--SET @DatabaseRoleName = '{Database Role Name}'
SET @DatabaseRoleName = 'DataEntryOperator'
SET NoCount  ON
DECLARE  @errStatement      [VARCHAR](8000),
         @msgStatement      [VARCHAR](8000),
         @DatabaseRoleID    [SMALLINT],
         @IsApplicationRole [BIT],
         @ObjectID          [INT],
         @ObjectName        [SYSNAME]
SELECT @DatabaseRoleID = [uId],
       @IsApplicationRole = CAST([IsapProle] AS BIT)
FROM   [dbo].[sysUsers]
WHERE  [Name] = @DatabaseRoleName
       AND ([IssqlRole] = 1
             OR [IsapProle] = 1)
       AND [Name] NOT IN ('public',
                          'INFORMATION_SCHEMA',
                          'db_owner',
                          'db_accessadmin',
                          'db_securityadmin',
                          'db_ddladmin',
                          'db_backupoperator',
                          'db_datareader',
                          'db_datawriter',
                          'db_denydatareader',
                          'db_denydatawriter')
IF @DatabaseRoleID IS NULL
  BEGIN
    IF @DatabaseRoleName IN ('public',
                             'INFORMATION_SCHEMA',
                             'db_owner',
                             'db_accessadmin',
                             'db_securityadmin',
                             'db_ddladmin',
                             'db_backupoperator',
                             'db_datareader',
                             'db_datawriter',
                             'db_denydatareader',
                             'db_denydatawriter')
      SET @errStatement = 'Role ' + @DatabaseRoleName + ' is a fixed database role and cannot be scripted.'
    ELSE
      SET @errStatement = 'Role ' + @DatabaseRoleName + ' does not exist in ' + Db_name() + '.' + CHAR(13) + 'Please provide the name of a current role in ' + Db_name() + ' you wish to script.'
    RAISERROR (@errStatement,16,1)
  END
ELSE
  BEGIN
    SET @msgStatement = '--Security creation script for role ' + @DatabaseRoleName + CHAR(13) + '--Created At: ' + CONVERT(VARCHAR,Getdate(),112) + REPLACE(CONVERT(VARCHAR,Getdate(),108),':','') + CHAR(13) + '--Created By: ' + Suser_name() + CHAR(13) + '--Add Role To Database' + CHAR(13)
    IF @IsApplicationRole = 1
      SET @msgStatement = @msgStatement + 'EXEC sp_addapprole' + CHAR(13) + CHAR(9) + '@rolename = ''' + @DatabaseRoleName + '''' + CHAR(13) + CHAR(9) + '@password = ''{Please provide the password here}''' + CHAR(13)
    ELSE
      BEGIN
        SET @msgStatement = @msgStatement + 'EXEC sp_addrole' + CHAR(13) + CHAR(9) + '@rolename ''' + @DatabaseRoleName + '''' + CHAR(13)
        PRINT 'GO'
      END
    SET @msgStatement = @msgStatement + '--Set Object Specific Permissions For Role'
    PRINT @msgStatement
    DECLARE _sySobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
    SELECT DISTINCT ([sySobjects].[Id]),
                    '[' + User_name([sySobjects].[uId]) + '].[' + [sySobjects].[Name] + ']'
    FROM   [dbo].[sysProtects]
           INNER JOIN [dbo].[sySobjects]
             ON [sysProtects].[Id] = [sySobjects].[Id]
    WHERE  [sysProtects].[uId] = @DatabaseRoleID
    OPEN _sySobjects
    FETCH NEXT FROM _sySobjects
    INTO @ObjectID,
         @ObjectName
    WHILE @@FETCH_STATUS = 0
      BEGIN
        SET @msgStatement = ''
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 193
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'SELECT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 195
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'INSERT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 197
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'UPDATE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 196
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'DELETE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 224
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'EXECUTE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 26
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'REFERENCES,'
        IF len(@msgStatement) > 0
          BEGIN
            IF RIGHT(@msgStatement,1) = ','
              SET @msgStatement = LEFT(@msgStatement,Len(@msgStatement) - 1)
            SET @msgStatement = 'GRANT' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseRoleName
            PRINT @msgStatement
          END
        SET @msgStatement = ''
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 193
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'SELECT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 195
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'INSERT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 197
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'UPDATE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 196
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'DELETE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 224
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'EXECUTE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 26
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'REFERENCES,'
        IF len(@msgStatement) > 0
          BEGIN
            IF RIGHT(@msgStatement,1) = ','
              SET @msgStatement = LEFT(@msgStatement,Len(@msgStatement) - 1)
            SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseRoleName
            PRINT @msgStatement
          END
        FETCH NEXT FROM _sySobjects
        INTO @ObjectID,
             @ObjectName
      END
    CLOSE _sySobjects
    DEALLOCATE _sySobjects
    PRINT 'GO'
  END