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;
