Giving Elevated permissions to developers?

Question

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 opinion.

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

Answer 1

Is it a good idea to give any elevated permissions to developers?

It's a question that is very difficult, and depending on whom you ask may get widely different answers.

In the place where I am, we are a small group of senior developers for our system, and three of us are sysadmin on the production server. But as I said, we are all senior. Furthermore, this place does not really a true DBA team, but the IT person responsible for the server trusts us to do the right thing. And after all, we wrote the system and we are the one who are in the know.

For many years I worked, first as an employee and then as a consultant, for an ISV who developed a large system that was critical for our customers. For most of the customers I had access to their production environment with sysadmin access, and many of my colleagues had the same. That was probably to high permission level - db_owner should have been OK in most cases.

So there are two answers from my professional career in favour. But there are many DBAs out there who would answer "Never in my life! Devs should not be sysadmin even on the dev server". As you may guess, I have never worked as a DBA, I've always been in a developer role.

There are many layers here. One layer is about the skills of the developer in question. A developer whose focus is .NET and Java and only does SQL Server left-handedly is more likely to cause a mess than a developer who is focused on SQL. Another layer is what methods your organisation want to embrace. DevOps has been popular the past decade, and with DevOps, developers are also involved in operations. Which is a good thing. Having silos with application teams and admin teams is less efficient and often results in a lot of finger-pointing than if the same team is responsible both for development and the production environment for the application.

But how you should work in your organisation is, I assume, over your pay grade. If your boss and your boss's boss have decided that you should work in silos, silos it is. And in that case granting developers elevated permissions... It may be something you want to discuss with your manager or a security manager before you go ahead.

As for your idea of packaging the permission in a stored procedure, that is absolutely a good idea. But EXECUTE AS OWNER is not a good tool to use for this, least of all for server permissions. (As you found out the hard way.) Instead the way to go is certificate signing. This is a bit complicated, not the least for server permissions, but once you master it, it is a very powerful method. I have an article on my web site where I discuss it in great detail: https://www.sommarskog.se/grantperm.html The article includes a script to automate the signing process for server-level permissions. (You only need to read the first five chapters to learn how to use the technique. But if you proceed, you will also learn why EXECUTE AS is an inferior choice.)