How to Generate Grant Permission Script to User on Database Objects

Script :Grant Permisison on User Object to User (Read Only Permission)

The will help to generate Grant Select,Execute & View permission to user on dataabse objects.We use this script when we have to give read only permission to users on prodcution databases.

—Select Permisison on User  Table— DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE = ‘U’ AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables   —Execute Permission on User Stored Procedure—- GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT EXECUTE ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE = ‘P’ AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables   —Select Permission on User View—– GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE = ‘V’ AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables  —Select Permission on User Function (Table-Valued-Function)—- GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE IN (‘TF’) AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables —Select Permission on User Function (Scalar-Valued-Function)—- GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT EXECUTE ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE IN (‘FN’) –AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables —Grant View Defination on User Types— GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT VIEW DEFINITION ON TYPE :: ‘ + ‘dbo’ +’.’ + st.NAME + ‘ TO ‘+@login FROM sys.types st where st.schema_id=1 SELECT * FROM @tables