Search

Thursday, July 28, 2011

Set execute permissions to all sp in one database

Fast way to set execute permissions to all stored procedures in one database:

DECLARE @username varchar(32)
SET @username = 'VCN\IT-GOT-UTS-DA'
DECLARE sproc_cursor CURSOR
FOR
select o.name, u.name from sysobjects o,sysusers U where o.type = 'P' and u.uid= o.uid
OPEN sproc_cursor
DECLARE @Procname sysname
DECLARE @ProcUserName varchar(30)
FETCH NEXT FROM sproc_cursor INTO @Procname,@ProcUserName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC('GRANT EXECUTE ON '+@ProcUserName +'.' + @Procname + ' TO [' + @username +']')
FETCH NEXT FROM sproc_cursor INTO @Procname,@ProcUserName
END
CLOSE sproc_cursor
DEALLOCATE sproc_cursor

No comments:

Post a Comment