October 20, 2010 at 2:09 am
HI Everyone,
Any one out there,
Please advise me the alternate permissions other than the SYSADMIN to grant the user to debug sql stored procedures
October 20, 2010 at 8:26 am
What are you trying to do?
Simply debugging the procedure, which means validating that it runs correctly and does what is requested of it, just requires access to the database and tables referenced in that procedure.
Done.
If you mean that you want to look at DMOs to troubleshoot (not debug) performance or locking, blocking, whatever, then you will have to give VIEW SERVER STATE because the most interesting DMOs are server-wide. You may also need to give VIEW DATABASE STATE to the databases in question, depending on what you need.
You can also grant ALTER TRACE to allow the user to set up server-side trace events, again, assuming troubleshooting, not debugging.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2010 at 12:24 pm
Thank you for the reponse,
Here i need to grant the user to DEBUG the stored procedure,but that permission should not be SYSADMIN on sqlserver side.
regards,
praveen
October 25, 2010 at 2:14 pm
October 25, 2010 at 6:32 pm
Hi,
DDL admin doesnt work for execution of stored procedures.Please help me further on this.
regards,
praveen
October 26, 2010 at 5:30 am
In order to execute stored procedures you have to GRANT EXECUTE ON something to the user to enable them execution privileges. You can grant execution rights to individual procedures. Another way is to grant execution rights to a particular schema. Then they can execute all procedures in that schema.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2013 at 2:44 pm
I am still not clear, I need to give a developer (who is DBO in that DB) privs to DEBUG a stored procedure using SQL Management Studio. In SQL 2008 R2, what are all permissions/roles I need to set for that user.
--Prabhu
June 4, 2013 at 3:02 pm
The SSMS debug 'feature' requires that the person using it is sysadmin.
To be honest, that 'feature' is probably one of the worst debuggers around, there are much better ways of running and testing code that take a little more manual work but have less problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply