Granting access to all objects in a database role.

This is my favorite script to grant access to database objects, specifically for development team. It would be tough to manage permission sometimes by creating individual accounts and granting access to database objects directly. This is much better way to grant permission hope this helps. Copy past the complete query and you have to edit few portions of the script as necessary.


DECLARE
        @LoginName VARCHAR(128),
        @DBUserRole VARCHAR(128),
        @DatabaseName VARCHAR(100),
        @SQLScript VARCHAR(6000)

SET @LoginName = 'Login\login'  -- Windows Login DOMAIN\Username
SET @DBUserRole = 'ROLE_for_developers' -- This role is for developer access to the objects

IF EXISTS (SELECT * FROM master..syslogins WHERE [name] = @LoginName)
BEGIN
        DECLARE DatabaseCursor CURSOR FOR
                SELECT [name] FROM master..sysdatabases
                WHERE  name in  ('database1','database2')  -- or you can just specify one database name = ''
ORDER BY [name]
        OPEN DatabaseCursor
        FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

        WHILE @@FETCH_STATUS = 0
                BEGIN

                        PRINT 'Updating Database:  ' + @DatabaseName + ''

                        SET @SQLScript = '
                                USE ' + @DatabaseName + '
                                IF NOT EXISTS (SELECT * FROM ' + @DatabaseName + '..sysusers WHERE [name] = ''' + @DBUserRole + ''')
                                        EXEC sp_addrolemember '''+ @DBUserRole + ''',''' + @LoginName + ''''
                                                     
                                                          
                        PRINT @SQLScript
                        --EXEC (@SQLScript)

                        -- Modify script below to change object types or type of permissions: U = User table, etc.
                        SET @SQLScript = '
                        USE ' + @DatabaseName + '
                        DECLARE
                                @TableName VARCHAR(100),
                                @SQLScript VARCHAR(4000),
                                                @type varchar(20)

                        DECLARE TableCursor CURSOR FOR
                                SELECT [name], type FROM ' + @DatabaseName + '..sysobjects WHERE type in (''U'',''V'',''P'',''FN'',''SN'',''TF'') ORDER BY [name]
                        OPEN TableCursor
                        FETCH NEXT FROM TableCursor INTO @TableName,@type
                        WHILE @@FETCH_STATUS = 0
                                BEGIN
                                                      set @SQLScript =  Case
                                                                                    when @type = ''U'' then ''GRANT SELECT,References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type =''V'' then ''GRANT SELECT,References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''P'' then ''GRANT Execute, References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''FN'' then ''GRANT Execute, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''TF'' then ''GRANT Select, References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''                           
                                                                                    when @type = ''SN'' then ''GRANT Select, Execute, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                End
                                      PRINT @SQLScript
                                        --EXEC(@SQLScript)

                                        FETCH NEXT FROM TableCursor INTO @TableName,@type
                                END
                        CLOSE TableCursor
                        DEALLOCATE TableCursor'

                     --PRINT @SQLScript
                       EXEC (@SQLScript)

                        FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

                END

        CLOSE DatabaseCursor
        DEALLOCATE DatabaseCursor
END
ELSE PRINT 'Login ' + @LoginName + ' does not exist.'

-Pramod K