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