Home > SQL > Code For Assign Permission to Tables,Views and Store procedure

Code For Assign Permission to Tables,Views and Store procedure

DECLARE @ObjectName varchar(100) = ''
			,@QUERY varchar(150) = ''

	WHILE EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE Name > @ObjectName 
        AND TYPE IN ('P', 'U', 'V'))
	BEGIN
		SELECT TOP 1
			 @QUERY = 'GRANT' + CASE WHEN TYPE = 'P' THEN '
                          EXECUTE ' ELSE ' SELECT ' END + 
                         'On [dbo].['+ NAME +'] TO KABCO'
			,@ObjectName = NAME
		FROM SYS.OBJECTS
		WHERE Name > @ObjectName
		AND TYPE IN ('P', 'U', 'V')
		ORDER BY TYPE, Name

		PRINT (@QUERY)
		EXEC (@QUERY)

		SET @QUERY = ''
	END
Advertisements
Categories: SQL
  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: