January 23, 2020 at 8:19 pm
QA is requesting for permission to execute store proc's in database against UAT to test some functionality, since we don't have QA server. I am ok with developers have elevated rights in dev. For UAT, i won't be very comfortable granting exec rights. Does anyone has any inputs for alternate solution?
January 23, 2020 at 8:27 pm
UAT is a testing area and (it would seem that) executing procs could be considered part of that testing.
As long as the devs can't make schema changes, I'm not sure there's a problem. What are you worried about, specifically?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 23, 2020 at 8:39 pm
Thanks. I don't have any thing specific in my mind. I am making sure if there is anything to worry about. However, i am also making sure to grant access to specific store procs based on the role rather than granting permissions to all procs in the database unless it is needed.
January 23, 2020 at 8:50 pm
There's nothing inherently wrong with granting them permissions to execute a SP in UAT.
But that raises the question of how the procs executed in production? Shouldn't QA be testing them as closely as possible to how they'll actually run?
January 23, 2020 at 9:18 pm
That sounds to be a valid point. I really appreciate it. All i know is sql server you can grant access to execute store proc and is their anything specific rights to compile? Or they both same?
January 23, 2020 at 10:20 pm
Looking for better way to grant recompile store procs permissions? Any advise?
January 23, 2020 at 11:04 pm
Looking for better way to grant recompile store procs permissions? Any advise?
My advise would be to learn more about what it means to recompile, when and why you would want to use it, the difference between automatic and manual recompile and everything in between including parameter sniffing.
Sue
January 23, 2020 at 11:20 pm
Sorry i mean to say compile. Compile and execute access for SP's.
January 23, 2020 at 11:29 pm
Based on your questions though, you don't really know what it is. That's the point. Why grant permissions on something you don't know anything about? Seems that's just as dangerous as what you seem to be trying to avoid. Pretty often you need to take the time and look things up and figure out what they mean, what they are, how they work, etc. It's part of being a good DBA. Or you can skip that part and just have the title.
Sue
January 23, 2020 at 11:29 pm
Also, my understanding is Execute permission is not sufficient for compile a SP? If this option is used when the procedure definition is created, it requires create procedure permissions in the database and ALTER permission on the schema in which the procedure is being created. If this option is used in an EXECUTE statement, does it requires only execute permissions on the procedure?
January 23, 2020 at 11:38 pm
I am sorry about the confusion.
January 23, 2020 at 11:53 pm
Thanks Sue! I totally agree what you said.
January 24, 2020 at 1:27 pm
Wait, wait, wait.
There isn't a compile of procedures in SQL Server. So that's not something you can grant permission for. There is a process where SQL Server will "compile" an execution plan for a query. You can also see a "recompile" of the plan.
You can CREATE, ALTER, DROP, EXECUTE a stored procedure. Is your QA team asking to execute procedures? I'd say yes, let them execute procedures. Are they asking to CREATE or ALTER procedures in QA? I'd say no, that's what development is for. Code changes should not be happening in QA independent of a development process and source control (which I assume you have for your database code, and if not, get it).
"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
January 24, 2020 at 9:23 pm
Thank you!
July 1, 2020 at 9:49 pm
QA is requesting for permission for write access on UAT server. I agree that UAT is a testing area and executing procs could be considered part of that testing. But write access is in question. But that raises the question how the testing is done in Production without write access? Shouldn't QA be testing them as closely as possible to how they'll actually run? Any inputs? Thanks in advance!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply