Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Grant Mass permissions

By mobasha B, 2009/04/09

the first procedure GrantOnSchemaToRole will create a role and grnat it user defined permission

on the database schemas.

example:

exec GrantOnSchemaToRole @RoleName='db_Execute', @Permission='execute', @DatabaseName='TestDB'

this will create a role named db_Execute in the database TestDB and grant this role execute permission on all of the DB Schemas.

the sconed procedure GrantOnSchemaToRoleAllServerDBs will run GrantOnSchemaToRole for every database on the server.

        
CREATE PROCEDURE [dbo].[GrantOnSchemaToRole]
 (
 @RoleName VARCHAR(50),
 @Permission VARCHAR(50),
 @DatabaseName VARCHAR(100)
 )
AS 
 BEGIN
 DECLARE @RoleCreation VARCHAR(500),
 @RoleCreationSafe VARCHAR(500),
 @GrantPermissionOuter VARCHAR(1500) ;
    
    
 SET @RoleCreation = 'use [' + @DatabaseName + ']' + CHAR(13)
 + 'IF NOT exists 
(
SELECT * FROM sys.[database_principals]
WHERE [principal_id]<16384
AND TYPE=''R''
AND [is_fixed_role]=0 and name like ''' + @RoleName + '''
)
Create Role [' + @RoleName + '] AUTHORIZATION [dbo]' ;
 set @RoleCreationSafe = REPLACE(replace(@RoleCreation, ';', ''), '-','') 
    
 EXECUTE ( @RoleCreationSafe )
    
 SET @GrantPermissionOuter = '
        
        DECLARE @InnerRoleName VARCHAR(50),@GrantPermissionSafe VARCHAR(500),@GrantPermission VARCHAR(500);
                DECLARE Cur891 CURSOR FOR SELECT name FROM ['
 + @DatabaseName + '].sys.[schemas]
        WHERE [schema_id]<16384
        AND [principal_id]=1
        OPEN Cur891
        FETCH NEXT FROM Cur891 INTO @InnerRoleName
        WHILE(@@FETCH_STATUS=0)
        BEGIN
            SET @GrantPermission=''USE [' + @DatabaseName + ']'
 + CHAR(13) + '
                                GRANT ' + @Permission
 + ' ON SCHEMA::[''+@InnerRoleName+''] TO [' + @RoleName
 + ']''
            set @GrantPermissionSafe=REPLACE(replace(@GrantPermission,'';'',''''),''-'','''')                    
            exec (@GrantPermissionSafe)        
            
        FETCH NEXT FROM Cur891 INTO @InnerRoleName    
        END
        CLOSE Cur891
        DEALLOCATE Cur891'
 EXECUTE ( @GrantPermissionOuter )

 END
go

CREATE PROCEDURE [dbo].[GrantOnSchemaToRoleAllServerDBs]
 (
 @RoleName VARCHAR(50),
 @Permission VARCHAR(50)
 )
AS 
 BEGIN
DECLARE @databaseName VARCHAR(100);

DECLARE cur5567 CURSOR FOR
SELECT name FROM sys.[databases]
WHERE [database_id] NOT IN (1,2,3,4)
AND [source_database_id] IS NULL AND [is_read_only]=0 AND [state]=0
and ;
OPEN cur5567;
FETCH NEXT FROM cur5567 INTO @databaseName;
WHILE(@@FETCH_STATUS=0)
BEGIN
    
    EXEC ADB.dbo.[GrantOnSchemaToRole] @RoleName,@Permission,@databaseName
    FETCH NEXT FROM cur5567 INTO @databaseName;
END
CLOSE cur5567;
DEALLOCATE cur5567;
END