November 23, 2023 at 4:24 pm
Hi All,
Is it a good idea to give any elevated permissions to developers?
In one of the sub-prod environments, Dev team is running some newly developed stored procedures from SSMS and Frontend UI.
When they see the blocking they are reaching out to us and we are providing where it is getting blocked and eventually KILLing the spid. They make the changes and re-running multiple times and reaching out to clear blocking multiple times.
So, got an idea of writing below stored proc as EXECUTE AS OWNER and let the dev team clear the blocking. Owner login is a part of sysadmin role. I tried to create a test user and given EXECUTE permission on the stored proc but it is doing nothing when try to run it as 'test' login. Given connect permission to 'test' login in master database.
use master
go
CREATE PROC USP_ClearBlocking
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @sqlcmd AS NVARCHAR(MAX) = N'';
;
WITH My_CTE AS
(
SELECT distinct blocked as blocking_session_id from sys.sysprocesses WHERE blocked <> 0
)
SELECT distinct @sqlcmd = @sqlcmd + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.sysprocesses AS s
INNER JOIN My_CTE AS BS
ON s.spid = BS.blocking_session_id
where db_name(dbid) = 'testdb' --//change the db name as desired
--PRINT @sqlcmd
EXEC sp_ExecuteSQL @sqlcmd
end
GO
GRANT EXECUTE ON OBJECT::USP_ClearBlocking TO [test];
GO
Question 1) Is there any security risk doing so or we should'nt be doing it? Please provide honest opnion.
Question 2) How to make it work. Am I missing any permission. I dont want to give any ALTER ANY CONNECTION or processadmin or sysadmin.
Regards,
Sam
November 23, 2023 at 10:09 pm
My opinion - blocking isn't something that needs a DBA to step in. Or at least it SHOULDN'T require a DBA to step in. Blocking should be temporary. If the blocking is long, something is probably wrong. If it is self-blocking, then chances are something went wrong when the query went parallel or the user wrote a really obscure query to cause self blocking. If it is not self-blocking, it could be caused by a long running transaction, and I'd look at committing or rolling back that transaction (if possible) before killing it (which essentially does a rollback). BUT the reason I'd want the commit/rollback to happen by the user running the query - that way they don't have a surprise of their query failing and not know why.
I also wouldn't write a script for end users to kill blockers as then they may get stuck with long rollbacks. Plus, it is never nice to kill someone else's SPID. Killing your own if you are self-blocking (for example) or running multiple queries that block each other, sure that's fine. But I would not want my developers or myself killing a blocking SPID without understanding what is going on.
If they REALLY need that level of control over the system, I would either spin up something for each developer to use exclusively, or if that isn't an option, I'd look at having them set up their own environment to develop against locally with sample data but live data structures. Advantage to that is they will only block themselves and if you need to do a full refresh of the test/dev environment, you won't blow out any work.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply