Deny SA account for Developers in Microsoft SQL Server

Feel yourself a lucky guy when you are a DBA and you never met an environment where all the users, developers, services, applications and everything access the SQL Server via the SA (system administrator) account.

Unfortunately, usage of SA limits DBA's (and DevOps) toolset for managed releases, secure database and server auditing and avoiding human errors. If you are connected to SQL Server with SA account then you explicitly have permission for every possible command, operation and functionality. You cannot limit the permission set of SA. The only possibility is to disable SA globally on the whole server.

But while SA is a commonly used login across the company than you have a good chance that all services, web applications connect to your SQL Service via SA. So disabling the SA account globally is not a real solution.

Anyways it is not a goal to deny access for developers to the Live database server. We should accept that sometimes developers must have access to (at least read) the Live or Production database. This article is not about to deny developers from SQL Server. It is just about controlling the access and give only the minimal required permissions.

Let's define goals first:

• User/Developers must not connect to SQL Server by SA account only their own Windows Domain user account (Windows Authentication).
• Applications, services, websites, ETL or reporting processes can still connect to SQL Server by SA if they are running on a dedicated server and not on a user's desktop.
• If a user has a remote desktop access on SQL Server then that user can connect to SQL Server locally by SA. This is a spare solution in case of any windows authentication problems.

We need the following components:
1. Server white list: these server machines host applications, services, ETL processes etc. This list contains the domain name of the server and we place it into a table in msdb database in SQL Server.

USE [msdb]
GO

CREATE TABLE [dbo].[HostWhiteList](
        [HostName] [varchar](128) NOT NULL PRIMARY KEY CLUSTERED
)

2. Windows domain group: for developers and users who need to connect to SQL Server. Of course, SQL Server operation system must be entered into that AD (a domain administrator can do it). In this example, we use Domain Users which is a default group in all AD domains. This contains all users in a domain.

3. A login for that AD group: this (Windows Authenticated) login will ensure the access for users arriving from anywhere. We need to run the following code. Replace DOMAIN with your current domain and Domain Users to your custom group:

CREATE LOGIN [DOMAIN\Domain Users] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

 Then grant permissions to this login:

ALTER SERVER ROLE [dbcreator] ADD MEMBER [DOMAIN\Domain Users]
ALTER SERVER ROLE [processadmin] ADD MEMBER [DOMAIN\Domain Users]

 If developers used to have access to everything (all databases and objects) then grant them (except master and distribution):

EXEC sp_msforeachdb '
        IF ''?'' NOT IN (''master'', ''distribution'')
                BEGIN
                USE [?];
                CREATE USER [DOMAIN\Domain Users] FOR LOGIN [DOMAIN\Domain Users];
                ALTER ROLE [db_owner] ADD MEMBER [DOMAIN\Domain Users];
                END

   

'

4. Login trigger on the server: this is the most important component. This prevents logins with sa from users desktop:

USE [master]
GO

CREATE TRIGGER [TR_ServerLogin]
ON ALL SERVER
FOR LOGON
AS
SET NOCOUNT ON;
IF (
        SYSTEM_USER = 'sa'
        AND
        (EXISTS(SELECT * FROM msdb.dbo.HostWhiteList WHERE HostName = HOST_NAME()) OR HOST_NAME() = SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
   )
   OR
   (
        SYSTEM_USER <> 'sa'
   )
        RETURN;
ROLLBACK;
GO

ENABLE TRIGGER [TR_ServerLogin] ON ALL SERVER

   

GO

WARNING! Don't change this trigger code otherwise you can ban yourself from the server even if you are an administrator!

That's all. After that, we should maintain the Whitelist table in msdb.
If any of users try to connect to SQL Server by SA then they will get the following message:


5. Disable remote desktop access for users: this option remains for administrators.

WARNING! Don't change the trigger code. You can ban yourself from the server using SA.
If this happens then you can use DAC (Dedicated Admin Connection). Login to the server via DAC disables login triggers.