December 19, 2012 at 12:34 pm
Is there a way to grant a user to alter, view and execute all the stored procedure in a database?
I have set him as a db_writer, but I don't think that includes above access?
Thanks
December 19, 2012 at 8:49 pm
You can use the code as follow which use cursor to grant permission.
declare @sp_name nvarchar(300)
declare @sql nvarchar(300)
declare cursor_name cursor
for select quotename(b.name)+'.'+ quotename(a.name)
from sys.objects a inner join sys.schemas b on a.schema_id=b.schema_id where type='p'
open cursor_name
fetch next from cursor_name into @sp_name
while @@fetch_status=0
begin
print @sp_name
set @sql=N'GRANT ALTER,execute,view definition ON '+@sp_name+' TO ***user***'
exec sp_executesql @sql
fetch next from cursor_name into @sp_name
end
close cursor_name
deallocate cursor_name
December 20, 2012 at 10:24 am
Perhaps this may be simpler:
use [YourDB]
GO
GRANT ALTER, EXECUTE, VIEW DEFINITION TO [DOMAIN\User]
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply