How to Avoid the 'Abuse' of SysAdmin by Applying User Defined Roles in SQL 2012 (and keep Exec.s and Auditors Happy)

This will not be a typical post, just a dive right away into Data Access Language Code, which provides a method to avoid the SysAdmin fixed Server role in SQL Server for DBAs and Monitoring/Auditing Accounts, thanks to extensive explicit permissions and taking full advantage of SQL 2012-4 User Define Server Roles or Flexible Server Roles.

-- This script would be a required step to do post instance install and to apply flexible server roles

-- We are to apply this as a security policy in production environments, and then perform validation 

-- Could be applied on some Dev/UAT servers

-- start with a rollback / back-out - or clean out roles to start again (alternatively skip to line 50)

USE [master]

GO

Drop Server Role DBAs;

Drop Server Role Monitoring;

go

-- Add DBAs back to fixed server role sysadmin, unless on servers that will not be managed by DBAs

ALTER SERVER ROLE [sysadmin] ADD MEMBER [Group1]

ALTER SERVER ROLE [sysadmin] ADD MEMBER [MonitoringAccount1]

ALTER SERVER ROLE [sysadmin] ADD MEMBER [MonitoringAccount2]

ALTER SERVER ROLE [sysadmin] ADD MEMBER [AuditingAccount]

GO

-- drop explicit rights in master, model and msdb also

USE [msdb]

GO

ALTER ROLE [ServerGroupAdministratorRole] DROP MEMBER [DBAGroup2]

ALTER ROLE [ServerGroupAdministratorRole] DROP MEMBER [DBAGroup1]

GO

ALTER ROLE [SQLAgentOperatorRole] DROP MEMBER [DBAGroup2]

ALTER ROLE [SQLAgentOperatorRole] DROP MEMBER [DBAGroup1]

GO-- not supposed to exist, but just in case

ALTER ROLE [db_owner] DROP MEMBER [DBAGroup2]

ALTER ROLE [db_owner] DROP MEMBER [DBAGroup1]

GO

use model

go

GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES

         on schema::dbo to [DBAGroup2]

GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES

         ON SCHEMA::[dbo] TO [DBAGroup1]

GO -- don't forget you can easily enough do DENY permissions too to prevent data modificación 

use master

go

-- All sections of this Security Hardening should correspond to a master Document/ed procedure

ALTER LOGIN [sa] enable

GO

use [Master]

go

drop USER [AuditingAccount] FOR LOGIN [AuditingAccount]

drop USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]

drop USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]

--- END CLEAN UP / Rollback of Role Security hardening

-- BEGIN SQL Security Hardening

--- disable SA, but do not drop it, maybe needed for service packs or for backout in Startup -m option (single-user mode)

ALTER LOGIN [sa] disable  -- ALTER LOGIN [sa] enable

GO

-- before applying any security policy, ensure BUILTIN\Administrators not there

USE MASTER
GO

IF EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’BUILTIN\Administrators’)

DROP LOGIN [BUILTIN\Administrators]

GO

-- New in SQL 2012 - User Defined Server Roles, begin to take advantage of them

-- using roles rather than granting access to individuals, is a best practice in itself,
-- and the flexibility of user defined roles has become essential for many reasons
-- not limited to but including auditing, compliance, best management practices

Create Server Role DBAs Authorization [securityadmin]; 

-- where SecurityAdmin contains just a few who have FireCall IDs (elevated accounts, for rare use)

-- example with a few DBAs

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’SeniorSQLDBA1’)
CREATE LOGIN [SeniorSQLDBA1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’SeniorSQLDBA2’)

CREATE LOGIN [SeniorSQLDBA2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

go

-- add a few senior dbas to [securityadmin] only for now (unless Super User/System Admin account exists)

ALTER SERVER ROLE [securityadmin] ADD MEMBER [SeniorSQLDBA2]

ALTER SERVER ROLE [securityadmin] ADD MEMBER [SeniorSQLDBA1]

-- these senior dbas should backup each other in case of role issues and revised grants have to be applied

GO

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’DBAGroup1’)

CREATE LOGIN [DBAGroup1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’DBAGroup2’)

CREATE LOGIN [DBAGroup2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]

CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]

GO

-- add DBA groups to the role

Alter server role DBAs add member [DBAGroup2]

-- we could use a subset of the following grants for different 'levels' of DBAs
-- (i.e. a JuniorDBA flexible server role)

Alter server role DBAs add member [DBAGroup1]

-- now lock down for operations we as DBAs should not be doing anyway

-- do not forget to give WITH GRANT rights when necessary

-- (please validate in the GUI afterwards that DBAs role has the correct DAL)

-- (all necessary permissions to do DBA job, minus unnecessary privileges)

GRANT ADMINISTER BULK OPERATIONS TO [DBAs] WITH GRANT OPTION

GRANT ALTER ANY CONNECTION TO [DBAs] WITH GRANT OPTION

GRANT ALTER ANY CREDENTIAL TO [DBAs] WITH GRANT OPTION

GRANT ALTER ANY DATABASE TO [DBAs] WITH GRANT OPTION

GRANT Shutdown to DBAs

GRANT control server TO [DBAs]

-- Luckily Control Server permission respects the following DENYs
-- which is not the case for sysadmin fixed role

GRANT ALTER ANY EVENT NOTIFICATION TO [DBAs] WITH GRANT OPTION

GRANT ALTER ANY EVENT SESSION TO [DBAs] WITH GRANT OPTION

GRANT ALTER RESOURCES TO [DBAs]

GRANT ALTER SERVER STATE TO [DBAs]

GRANT ALTER SETTINGS TO [DBAs]

GRANT AUTHENTICATE SERVER TO [DBAs]

-- Grants or denies the ability to use a particular signature across all databases on the server
-- when impersonation is used.

GRANT CONNECT SQL TO [DBAs] WITH GRANT OPTION

-- Grants or denies the ability to connect to the SQL Server.

-- All logins, when newly created, are granted this permission automatically

GRANT CREATE ANY DATABASE TO [DBAs] WITH GRANT OPTION

-- GRANT CREATE AVAILABILITY GROUP TO [DBAs] (if you have Av. Groups at all)

GRANT CREATE DDL EVENT NOTIFICATION TO [DBAs]

GRANT CREATE TRACE EVENT NOTIFICATION TO [DBAs]

GRANT VIEW ANY DATABASE TO [DBAs] WITH GRANT OPTION

GRANT VIEW ANY DEFINITION TO [DBAs]

GRANT VIEW SERVER STATE TO [DBAs]

GRANT ALTER ANY EVENT NOTIFICATION TO [DBAs]

GRANT ALTER ANY EVENT SESSION TO [DBAs]

GRANT ALTER ANY LOGIN TO [DBAs]  -- some may want this as a DENY

--- Now the explicit denys (options)

DENY ALTER ANY AVAILABILITY GROUP TO [DBAs]

DENY ALTER ANY ENDPOINT TO [DBAs]

DENY ALTER ANY LINKED SERVER TO [DBAs]

-- debatable regarding linked servers

DENY ALTER ANY SERVER ROLE TO [DBAs] 

--obviously, we want control on number or roles

DENY ALTER TRACE TO [DBAs]

DENY CREATE ENDPOINT TO [DBAs]

Deny impersonate on login::sa to DBAs

-- add any other accounts that are individual users on the server with elevated rights,
-- and the Service Account(s)
Deny impersonate on login::[SERVICEaccountNameHere] to [DBAs]

Deny Alter any Server Audit to DBAs

Deny Unsafe Assembly to DBAs;

GO

-- resolve master grants

USE [master]

GO

GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup2]

GRANT select, view definition, execute on schema::sys to [DBAGroup2]

GRANT EXECUTE ON xp_readerrorlog TO [DBAGroup2]  -- helpful to get DBAs to query the error log

GRANT EXECUTE ON sp_readerrorlog TO [DBAGroup2]

ALTER ROLE [db_datareader] ADD MEMBER [DBAGroup2]

GO

GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup1]

GRANT select, view definition, execute on schema::sys to [DBAGroup1]

GRANT EXECUTE ON xp_readerrorlog TO [DBAGroup1]

GRANT EXECUTE ON sp_readerrorlog TO [DBAGroup1]

ALTER ROLE [db_datareader] ADD MEMBER [DBAGroup1]

GO

-- resolve MSDB grants

USE [msdb]

GO

CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]

CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]

go

ALTER ROLE [ServerGroupAdministratorRole] ADD MEMBER [DBAGroup1]

ALTER ROLE SQLAgentOperatorRole ADD MEMBER [DBAGroup1]

GO 

ALTER ROLE [ServerGroupAdministratorRole] ADD MEMBER [DBAGroup2]

ALTER ROLE SQLAgentOperatorRole ADD MEMBER [DBAGroup2]

GO -- after roles, grant explicit rights to be sure nothing is missing

GRANT select, execute, CONTROL, REFERENCES on schema::dbo to [DBAGroup2]

GO

GRANT select, execute, CONTROL, REFERENCES on schema::dbo ON SCHEMA::[dbo] TO [DBAGroup1]

GO -- allow DBAs to be part of msdb ownership if you like

--ALTER ROLE [db_owner] ADD MEMBER [DBAGroup1]

GO -- not necessary since in above Admnistrator and Operator roles

ALTER ROLE [db_ssisadmin] ADD MEMBER [DBAGroup2]

go

-- resolve issues for all new databases created - fix MODEL database to include DBAs.

Use Model

GO

-- setup DenyData reader role by default for groups to cover Prod data constraint

CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]

CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]

-- all user databases for production, will have deny read on the data

GO

-- use roles even at the database level

CREATE ROLE [DBAs] AUTHORIZATION [dbo]

GRANT VIEW DATABASE State, execute, view definition TO [DBAs]

-- remove read access when necessary, but allow administration

-- add appropriate groups

ALTER ROLE [DBAs] ADD MEMBER [DBAGroup2]

ALTER ROLE [DBAs] ADD MEMBER [DBAGroup1]

-- and finally, requirements for some prod. environments

Deny select, insert, update TO [DBAs] 

GO

-- or deny select in prod user dbs this way

ALTER ROLE [db_denydatareader] ADD MEMBER [DBAGroup2]

ALTER ROLE [db_denydatareader] ADD MEMBER [DBAGroup1]

GO

-- WE DO NOT STOP THERE, CONTINUE with Monitoring Accounts
-- which traditionally have way too many Privileges

-- and the goal is to adhere to the principle of least privileges

use master

GO

-- create monitoring role and add accounts the monitor the servers

Create Server Role [Monitoring] Authorization [securityadmin]; 

-- the right logins in the container

CREATE LOGIN [MonitoringAccount1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

CREATE LOGIN [MonitoringAccount2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

CREATE LOGIN [AuditingAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

ALTER SERVER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]

ALTER SERVER ROLE [Monitoring] ADD MEMBER [MonitoringAccount2]

ALTER SERVER ROLE [Monitoring] ADD MEMBER [AuditingAccount]

-- minimum WMI permissions required, so add to local admins on the server too

GRANT CONNECT SQL TO [Monitoring]

GRANT CONTROL Server TO [Monitoring]

GRANT ALTER TRACE TO [Monitoring]

GRANT VIEW ANY DATABASE TO [Monitoring]

GRANT VIEW ANY DEFINITION TO [Monitoring]

GRANT VIEW SERVER STATE TO [Monitoring]

Grant CREATE DDL EVENT NOTIFICATION TO [Monitoring]

GRANT CREATE TRACE EVENT NOTIFICATION TO [Monitoring]

-- because we have granted control server, we must apply these DENY statements

DENY ALTER ANY AVAILABILITY GROUP TO [Monitoring]

DENY ALTER ANY ENDPOINT TO [Monitoring]

DENY ALTER ANY LINKED SERVER TO [Monitoring]

DENY ALTER ANY LOGIN TO [Monitoring]

DENY ALTER ANY SERVER ROLE TO [Monitoring] 

--obviously, we want control on number or roles

DENY CREATE ENDPOINT TO [Monitoring]

Deny impersonate on login::sa to [Monitoring]

-- add any other accounts that are individual users on the server that have Sysadmin rights
Deny impersonate on login::[SERVICEaccountNameHere] to [Monitoring]

Deny Alter any Server Audit to [Monitoring]

Deny Unsafe Assembly to [Monitoring];

-- Monitoring users need to be in Master and Model, MSDB as DataReader

Go
CREATE USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]

CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]

CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]

--

ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount1]

ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount2]

ALTER ROLE [db_datareader] ADD MEMBER [AuditingAccount]

-- or more explicitly
grant select, view definition, execute on schema::dbo to [AuditingAccount]

grant select, view definition, execute on schema::sys to [AuditingAccount]

GRANT EXECUTE ON xp_readerrorlog TO [AuditingAccount]

GRANT EXECUTE ON sp_readerrorlog TO [AuditingAccount]

GO
grant select, view definition, execute on schema::dbo to [MonitoringAccount1]

grant select, view definition, execute on schema::sys to [MonitoringAccount1]

GRANT EXECUTE ON xp_readerrorlog TO [MonitoringAccount1]

GRANT EXECUTE ON sp_readerrorlog TO [MonitoringAccount1]

GO

grant select, view definition, execute on schema::dbo to [MonitoringAccount2]

grant select, view definition, execute on schema::sys to [MonitoringAccount2]

GRANT EXECUTE ON xp_readerrorlog TO [MonitoringAccount2]

GRANT EXECUTE ON sp_readerrorlog TO [MonitoringAccount2]

GO
USE [msdb]

GO -- the options here for system databases are to either continue to create user defined roles, or used fixed if possible

CREATE USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]

CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount2]

CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]

GO
grant execute on schema::dbo to [MonitoringAccount1]
grant execute on schema::dbo to [MonitoringAccount2]
grant execute on schema::dbo to [AuditingAccount]

GO

-- improve this by adding appropriate role?

ALTER ROLE [db_datareader] ADD MEMBER [AuditingAccount] -- may need to use more elevate fixed roles here

ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount1]

ALTER ROLE [db_datareader] ADD MEMBER [MonitoringAccount2]

GO

-- add model for defaults similar DBA groups

Use Model

GO

CREATE USER [AuditingAccount] FOR LOGIN [AuditingAccount]

CREATE USER [MonitoringAccount1] FOR LOGIN [MonitoringAccount1]

CREATE USER [MonitoringAccount2] FOR LOGIN [MonitoringAccount12]

-- all user databases for production, will have deny read on the data

GO  -- create  monitoring database role

CREATE ROLE [Monitoring] AUTHORIZATION [dbo]

GRANT VIEW DATABASE State, execute, view definition TO [Monitoring]

-- remove read access when necessary, but allow administration

Deny select, insert, update TO [Monitoring]  -- requirements for some prod. environments

-- add appropriate groups or accounts for auditing or monitoring

ALTER ROLE [Monitoring] ADD MEMBER [AuditingAccount]

ALTER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]

ALTER ROLE [Monitoring] ADD MEMBER [MonitoringAccount1]

GO

USE [master]

GO

-- cleanup those who were in sysadmin

ALTER SERVER ROLE [sysadmin] DROP MEMBER [DBAGroup2]

ALTER SERVER ROLE [sysadmin] DROP MEMBER [DBAGroup1]

ALTER SERVER ROLE [sysadmin] DROP MEMBER [AuditingAccount]

ALTER SERVER ROLE [sysadmin] DROP MEMBER [MonitoringAccount1]

-- you can truly state that you have reduced the SysAdmin elevate account access to your auditors

GO
-- validate that only those who you want are noe in sysadmin

use master
go
exec sp_helpsrvrolemember 'sysadmin'
-- reduce members to bare minimum !!

-- new for sql 2012: check which services are listed:

select * from sys.dm_server_services

-- for each new application added, one should verify the wanted/identifiable
-- (and not unwanted) logins are viewed
select * from sys.dm_exec_connections


--final review of permissions 
SELECT * FROM sys.fn_builtin_permissions('SERVER')
ORDER BY permission_name;

-- again but at a different level
SELECT * FROM sys.fn_builtin_permissions('default')
ORDER BY permission_name;

-- database securable
SELECT * FROM sys.fn_builtin_permissions('database')
ORDER BY permission_name;