Home > Grant Permission to User On Database Objects > How to Generate Grant Permission Script to User on Database Objects

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

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: