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