March 14, 2003 at 1:36 am
A developer needs permission to execute any/all stored procedures in a given database. I can make him dbo (not too good an idea) or I can individually grant him Exec permission for each (of 70) stored procs in the database. (Seems like a lot of work.)
Is there another alternative I'm missing?
TIA,
Bill
March 14, 2003 at 1:44 am
No. You can create a role called db_executor or something of that sort and then run jobs to continually update that role's permissions as new stored procedures are created. Put the developer's user account in said role.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
March 14, 2003 at 9:56 am
You could create a cursor FOR SELECT name from sysobjects WHERE XTYPE = 'P' and then execute the Grant sentence to the user or the role posted before.
March 14, 2003 at 10:16 am
Try this stored proc with :
http://www.sqlservercentral.com/scripts/contributions/600.asp
EXEC spSetPermissionsGlobally @name = 'ROLE NAME',
@printonly = 0,
@revokeOldRights = 1,
@processViews = 0,
@processProcs = 1,
@processTables = 0,
@processFunctions = 0
Tim C.
//Will write code for food
Edited by - tcartwright on 03/14/2003 10:17:56 AM
Tim C //Will code for food
March 15, 2003 at 11:53 am
Set your QA to display results in text. Then run following Query.
select 'grant execute on '|name|' to yourusername' from sysobjects where type='sp'
Take the Results of this query and copy in the query window and run that. Make sure that reslut is displaying exact same number as number of stored procedures.
Check above query for Syntax. didn't try to run it, but I always use this trick when I have to give multiple privileges to the same user.
Edited by - hirenk on 03/15/2003 11:54:30 AM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply