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;

Managing Multiple Servers Thanks to Registered Server Lists & the Benefits

Since I changed organisations in January this year, I asked one of my colleagues to export his SQL Server Management Studio Registered Server list. Ever since March I have been updating our list on nearly a daily basis, since I believe this is very important to managing multiple SQL Servers. For DBA teams, I believe one should make the registered server list available for the rest of the team via a network share.
Note that you can open REGSRVR file with a text editor to search through the XML data, such as to figure out within which group a specific server resides.

Multiple people cannot update this list unfortunately (unless manually) thus I do my best to keep it up to date. Maybe, at a later date, we`ll be able to have this managed under a Central Management Server (CMS), but that`ll have to wait until we begin to manage through Policies from a single CMS server.

If you have not used Registered Servers in SQL Server Management Studio before, simply click View, Registered Servers. You will also notice the option Types, which groups by Database Engine, Analysis Services, Reporting Services, SQL Server Compact, and Integration Services. Our list has very few updated for anything other than the Database Engine Type, but that will change over time!
Remember to re-import it frequently if this is the case in your organisation: to do so, right click on top of the Registered Server list, Local Server Groups, and import from the a shared folder.

One of the nice features to take advantage of with a Registered Server list is querying across multiple servers.  By right clicking on any group, even the entire Local Group, you can select New Query to view, for example, Server Infrastructure details quickly.

 The first method to going essential server properties, as mentioned on Technet, is to execute the following parametr details on any SQL Server installation (I tested back to SQL 2000), by executing the following command.

xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount", "ProcessorType"
-- result set is a table, with a row for each parameter
-- thus if you execute xp_msver, you will have twenty rows

The second, and my preference as best practice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):

select serverproperty('MachineName') MachineName
,serverproperty('ServerName') ServerInstanceName
,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled
,serverproperty('productVersion') ProductBuildLevel
,serverproperty('productLevel') SPLevel
,serverproperty('Collation') Collation_Type
,serverproperty('IsClustered') [IsClustered?]
,convert(varchar,getdate(),102) QueryDate
,serverproperty('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName],
case
when exists (select * from msdb.dbo.backupset where name like '%backup%') then 'SQLBackupUsed'
else 'Local Copy_Only'
-- where you would replace the
-- strings with your respective third party or native backup solution
End

More examples? Sure:
I was asked the other day to find out how many physical sockets (not logical) that are used across servers, so one would write this:

SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM    sys.dm_os_sys_info ;

How about all cluster node details across a group of clustered servers:

SELECT * FROM sys.dm_os_cluster_nodes

--All non-background processes and their wait times across servers:
select * from sysprocesses where status <>'background'order by waittime desc
-- Read Error logs across servers
exec master.dbo.xp_readerrorlog

Thanks to SQL Server Central forums for the physical socket query, no wonder I enjoy blogging there also
...and thanks to Microsoft Premier Field Engineers for the last three during a recent Clustered Server resolution analysis issue.