Script :Grant Permisison on User Object to User (Read Only Permission)
A Handy script to quickly script to grant permission on database objects to user or role
go use databasename go --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