Change Permission on SP or View using T-SQL 1

[Ovatvvon]

I have hundreds of Views and Stored Procedures where I need to add View Definitions permissions to for the Public role for the database.

What would be the easiest way to set the permission for each View and SP for the DB Public Role short of manually going through each one in the GUI and setting it?

Is there an easy solution using T-SQL?

-Ovatvvon :-Q

 

[danvlas]

Get a list of views with:

SELECT name
FROM dbo.sysobjects
WHERE (xtype = 'v')

Using any technique, grab the name of the views from the resultset and build a text like:

GRANT SELECT ON [dbo].[View1] TO [public]
GRANT SELECT ON [dbo].[View2] TO [public]
GRANT SELECT ON [dbo].[View3] TO [public]
and so on

Copy the text, paste it in Query analyzer and you're done.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ptheriault]

When ever your dealing with a granting permissions to a massive amount of procs or views you should have a script to create the grant scripts.

Like this one here to grant exec on procs.

select 'GRANT EXEC ON ' + schema_name(schema_id) + 
'.' + OBJECT_NAME(object_id)
	+ ' TO user'
FROM sys.objects
WHERE type_desc = 'SQL_STORED_PROCEDURE'
AND is_ms_shipped = 0
ORDER BY name

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

also,
It's never a good idea to grant permissions to PUBLIC.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ovatvvon]

Thanks Paul.

I changed it to a specific user, instead of public, even though they already have permissions setup for public in other areas.

This is the script I created, let me know what you think please:

DECLARE @obj_name nvarchar(128), @sql nvarchar(200)

Declare curObjects Cursor For 
	SELECT [name]
	FROM [sysobjects]
	WHERE [xtype] IN (N'P', N'V') 
		AND [name] NOT IN (
			SELECT [name] 
			FROM [sys].[objects] 
			WHERE [type] IN (N'P', N'V') 
				AND [is_ms_shipped] = 1
		)
	ORDER BY [name]
OPEN curObjects
FETCH NEXT FROM curObjects INTO @obj_name
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT N'Altering Permissions for ' + @obj_name
	SET @sql = 'GRANT VIEW DEFINITION ON ' + @obj_name + ' TO DB_User'
	EXEC (@sql)
	FETCH NEXT FROM curObjects INTO @obj_name
END
CLOSE curObjects
DEALLOCATE curObjects
PRINT N'-------------------------------------------------'
PRINT N'Permissions on the database objects listed above have been successfully altered.'

The only thing is, when I included 'TR' (along with 'P' and 'V'), so it would grab triggers, it didn't seem to work for those, saying they don't exist, or I don't have permission. (FYI, I have SA permissions).

Thoughts?

-Ovatvvon :-Q

 

[Ovatvvon]

Actually, I think the errors I was seeing was due to ownership of the objects other than 'dbo', so it didn't recognize them.

Thanks - stars granted...


-Ovatvvon :-Q