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 User Define Server Roles or Flexible Server Roles. This way we can better manage security and avoid the over-use of sysadmin.
-- This scriptwould be a required step to do post instance install and to apply flexible
server roles
-- We are toapply this as a security policy in production environments, and then perform
validation
-- Could beapplied on some Dev/UAT servers
-- start with a rollback / back-out - or cleanout roles to start again (alternatively skip to line 50)
USE [master]
GO
Drop Server Role DBAs;
Drop Server Role Monitoring;
go
-- Add DBAsback 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
-- dropexplicit 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
use master
go
-- Allsections 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] FORLOGIN [MonitoringAccount1]
drop USER [MonitoringAccount2] FORLOGIN [MonitoringAccount2]
--- END CLEANUP / Rollback of Role Security hardening
-- BEGIN SQL Security Hardening
--- disable SA, but do not drop it, maybe needed for servicepacks or for backout in Startup
-m option (single-user mode)
ALTER LOGIN [sa] disable -- ALTER LOGIN [sa]enable
GO
-- before applying any security policy, ensureBUILTIN\Administrators not there
USE MASTER
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 takeadvantage of them
-- using rolesrather than granting access to individuals, is a best practice in itself,
-- but 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 [sysadmin];
-- where SecurityAdmin contains justa few who have FireCall IDs (elevated accounts, for
rare use)
CREATE LOGIN [SeniorSQLDBA1]FROM WINDOWS WITH DEFAULT_DATABASE=[master]
CREATE LOGIN [SeniorSQLDBA2]FROM WINDOWS WITH DEFAULT_DATABASE=[master]
go
-- add a fewsenior 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 backupeach other in case of role issues and revised grants have to be applied
GO
CREATE LOGIN [DBAGroup1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
CREATE LOGIN [DBAGroup2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
CREATE
USER [DBAGroup1]FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
GO
-- add DBAgroups 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 doinganyway
-- do notforget to give WITH GRANT rights when necessary
-- (pleasevalidate in the GUI afterwards that DBAs role has the correct DAL)
-- (allnecessary 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]
-- LuckilyControl 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 ordenies 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 ordenies 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
-- GRANTCREATE 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
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 toDBAs
-- add anyother accounts that are individual users on the server
Deny Alter any Server Audit to DBAs
Deny Unsafe Assembly to DBAs;
GO
USE [master]
GO
GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dboto [DBAGroup2]
GRANT select, view definition, execute on schema::sys to [DBAGroup2]
GRANT EXECUTE ON xp_readerrorlog TO [DBAGroup2]
GRANT EXECUTE ON sp_readerrorlog TO [DBAGroup2]
ALTER ROLE [db_datareader]ADD MEMBER [DBAGroup2]
GO
GRANT select, view definition, execute, CONTROL, REFERENCES on schema::dboto [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
USE [msdb]
GO
CREATE USER [DBAGroup1] FOR LOGIN [DBAGroup1]
CREATE USER [DBAGroup2] FOR LOGIN [DBAGroup2]
go
ALTER ROLE [ServerGroupAdministratorRole]ADD MEMBER [DBAGroup1]
ALTER ROLE SQLAgentOperatorRoleADD MEMBER [DBAGroup1]
GO
ALTER ROLE [ServerGroupAdministratorRole]ADD MEMBER [DBAGroup2]
ALTER ROLE SQLAgentOperatorRoleADD MEMBER [DBAGroup2]
GO -- after roles, grant explicit rights to be sure nothing ismissing
GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES
on schema::dbo to [DBAGroup2]
GO
GRANT select, insert, TAKE OWNERSHIP, view definition, update, execute, CONTROL, REFERENCES
ON SCHEMA::[dbo]TO [DBAGroup1]
GO -- allow DBAs to be part of msdbownership?
--ALTER ROLE [db_owner] ADD MEMBER [DBAGroup1]
GO -- not necessary since in above Admnistratorand Operator roles
--ALTER ROLE [db_owner] ADD MEMBER [DBAGroup2]
go
-- resolve issues for all new databases created - fix MODELdatabase to include DBAs.
Use Model
GO
-- setup DenyData reader role bydefault 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 onthe data
GO
-- use roleseven at the database level
CREATE ROLE [DBAs] AUTHORIZATION [dbo]
GRANT VIEW DATABASE State, execute, view definition TO [DBAs]
-- remove readaccess when necessary, but allow administration
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 dbsthis 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 leastprivileges
use master
GO
-- createmonitoring 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 onthe 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 theseDENY 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 anyother accounts that are individual users on the server
Deny Alter any Server Audit to [Monitoring]
Deny Unsafe Assembly to [Monitoring];
-- Monitoring users need to be in Master andModel, MSDB as DataReader
GoCREATE
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]
-- ormore 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 -- the options here for system databases are to eithercontinue 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]
GOgrant
execute onschema::dbo to [MonitoringAccount1]
grant
execute onschema::dbo to [MonitoringAccount2]
grant execute on
schema::dbo to [AuditingAccount]
GO
-- improvethis 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 modelfor 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 onthe data
GO -- create monitoring database role
CREATE ROLE [Monitoring] AUTHORIZATION [dbo]
GRANT VIEW DATABASE State, execute, view definition TO [Monitoring]
-- remove readaccess when necessary, but allow administration
Deny select, insert, update TO [Monitoring] -- requirements for some prod. environments
-- addappropriate 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
