Script the permissions of an OBJECT


Recently I came across a situation where in I had to scripyt out the permissions of all the stored procedures for a database.After lot of R&D came across a SP which was quite handy.
 
Solution :
 
sp_helprotect null, null,null,’o’ 
This SP will provide the required SP permissions when ran against the DB.
We can also write a cursor to accomplish this task and the code snippet is mentioned below :
DECLARE @name varchar(100)
DECLARE cur CURSOR local FOR
SELECT name from dbo.sysobjects
 WHERE type = ‘P’ AND category = 0
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_helprotect @name
FETCH NEXT FROM cur INTO @name
END
CLOSE Cur
DEALLOCATE Cur 

  

 


Advertisements

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