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