Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

grant permission to view/edit/execute all stored procedure Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 12:34 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
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
Post #1398618
Posted Wednesday, December 19, 2012 8:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:45 PM
Points: 26, Visits: 91
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

Post #1398752
Posted Thursday, December 20, 2012 10:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:53 PM
Points: 3,733, Visits: 7,072
Perhaps this may be simpler:
use [YourDB]
GO
GRANT ALTER, EXECUTE, VIEW DEFINITION TO [DOMAIN\User]
GO



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1399052
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse