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 

  

 



Error :SQL Server 2005 cluster config and trying to move database to a different drive/create a database “Only formatted files on which the cluster resource of the server has a dependency can be used.Either the disk resource containing the file is not present in the cluster group or the cluster”


For the SQL Server resource we need to add a dependency of the drive to which we are moving the database and log files.
 
1. Right click the SQL Server resource under Groups and click Properties.
 
2. Click on dependency tab and add the disk LUN.
 
Note : You will need to take the resource group offline to accomplish this solution and bring it back online once the change has been completed.