What to do when your Developers claim they need 'sysadmin' access

How many times has a developer come to you and informed you he must have 'sysadmin' rights to the database server(s) to do their job?

How many times has the dev manager come to you saying their developers can only do their work if they have 'sysadmin' rights to the database server(s)?

How many times have you also heard 'It's just the Dev Server', give the developers 'sysadmin'.

Do they really need it 'sysadmin' access? NOPE! NADA! NOT EVEN CLOSE!

There are several layers of access you can provide your developers without giving them any access close to 'sysadmin', and they can still adequately perform their daily developer duties.

DBA's still have to maintain the server, and if a developer is continually changing settings, it will be next to impossible to support, troubleshoot, and tune. DBA's need to have complete control because inevitably, they will need to restore tables from backups at least once a week when a developer forgets a 'where clause' among tracking down 'what has changed' since the last time the DBA looked at the server.

Let me lay out a few dangers of giving your developer sysadmin.

If a developer has been granted 'sysadmin', they are essentially in 'God mode'!

(Some of you may not get this reference, extra points if you do!)

This means they can do anything and everything to the server. The user would then have the ability to delete and export data, delete logs, drop and create databases, delete and add users, create a backdoor user, change permissions, crash the server, export encryption certificates, stop backups and cover their tracks while doing it.

Now, that we have hopefully scared you a little, let me show you how you can give developers the access they need to do their jobs without compromising your job or the database servers you are responsible for.

The below permission suggestions are intended only for development environments.

First, we will need to create or use an existing domain user group (or get your AD admin to create you one). In our example, we will be using 'jeffsworklaptop\developers'. We will need a 'developers' user account and add it to our user group from above. In our example, we will be using 'TestDevUser'. Next, go to SSMS (SQL Server Management Studio), connect to your database server and go to 'Security' and create a 'new login'.

Next, click on 'Search' and locate the user group.

Be sure to click on 'Object Types' and select 'Groups' as this option is not highlighted by default.

Type in the name of your group 'developers' and click 'Check Names'.

It will then validate your group name and change it to look something like this.

Click 'Ok'.

Next, move down to the 'User Mapping' and then select the database(s) your developers need access to. Check the database 'Map' checkbox, then go to the bottom window and select 'db_datareader', this will provide the developer access to read from the database tables. Next, check 'db_datawriter', this will give the developer access to insert and update tables in the database. Next, check 'db_ddladmin', this will provide the developer access to create tables, stored procedures, and for the adventurous, indexes, and triggers. Next click 'Ok'.

To script this out, click 'Script' at the top of the window before hitting 'ok'. My example is provided below.

USE [master]
GO
CREATE LOGIN [JEFFSLAPTOP\Developers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [Logging]
GO
CREATE USER [JEFFSLAPTOP\Developers] FOR LOGIN [JEFFSLAPTOP\Developers]
GO
ALTER ROLE [db_datareader] ADD MEMBER [JEFFSLAPTOP\Developers]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [JEFFSLAPTOP\Developers]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [JEFFSLAPTOP\Developers]
GO

The db_ddladmin role is relatively extensive. Depending on the needs of your application or company's use of SQL Server, you may or may not want some of these options. Here is a link to MS SQL's permission chart https://aka.ms/sql-permissions-poster for reference.

If you have advanced developers who want to see what is going on in the database server, you can let them see sp_who or sp_who2. You will need to grant them another layer or two of access. I use and highly recommend you use Adam Mechanic's awesome sp_whoisactive, which is configurable.

This is typically deployed in the master database, so if a developer tries to execute this they would see this message below.

First, you will need to grant the developer's group access to execute this stored procedure since it is in the master database.

Here is the script you will need to execute.

USE master
GO
GRANT EXECUTE ON OBJECT::dbo.sp_whoisactive TO [JEFFSLAPTOP\Developers]
GO

This isn't quite enough, however. After granting execute the user will see this message when executing.

Next, we will need to grant the user 'View Server State' by using this script below.

USE master
GO
GRANT VIEW SERVER STATE TO [JEFFSLAPTOP\Developers]
GO

Now that the developer's group has permissions, they can execute and see running processes on the server.

Now, the last typical scenario developers might need access to that I have come across is SQL Agent Jobs. There are three levels of access you can provide them depending on what your needs are from least to most privileged.

SQLAgentUserRole: This allows a user to create, edit, execute, and delete their own SQL Agent Jobs.

SQLAgentReaderRole: Does the above, plus allows the user to see other jobs, but not execute, edit or delete them.

SQLAgentOperatorRole: Does both above, plus allows the user to execute any jobs on the server.

Currently, any developer in the developer's user group doesn't see the SQL Agent, but it can see the database they have access to.

In SSMS, go to 'Security', then select 'Logins' and select your developer's user group, right mouse click and select 'Properties'.

Next, navigate to 'User Mapping' and select 'msdb' then scroll down in the 'database role memberships' windows and select the membership role which you need:

SQLAgentUserRole

SQLAgentReaderRole

SQLAgentOperatorRole

Here is the script below, which executes from above for msdb and then each possible role.

USE [msdb]
GO
CREATE USER [JEFFSLAPTOP\Developers] FOR LOGIN [JEFFSLAPTOP\Developers]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [JEFFSLAPTOP\Developers]
GO
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [JEFFSLAPTOP\Developers]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [JEFFSLAPTOP\Developers]
GO

Now, if you give the 'user role' the developer refreshes they will see the SQL Server Agent.

The developers can now create jobs, execute jobs and delete only jobs they have created.

If you give them 'Reader' or 'Operator' role you will see all of the jobs on the server. If you change the owner of the job, they will no longer be able to see or edit or execute based on their role.

This should satisfy most developers and allow them to move around freely on their database servers without causing any irreputable harm.

If you have any questions or tips and tricks you'd like to share feel free to leave them in the comments below.

Shout out to @pagerwho who reviewed my post for accuracy.