Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 User Define Server Roles or Flexible Server Roles. This way we can better manage security and avoid the over-use of sysadmin. 

-- 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
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
IF EXISTS (SELECT * FROM sys.server_principals
WHERE name = NBUILTIN\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,
-- 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 just a few who have FireCall IDs (elevated accounts, for rare use)

-- add if not exists here
CREATE LOGIN [SeniorSQLDBA1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
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
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 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
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
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]
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, 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 msdb ownership?
--ALTER ROLE [db_owner] ADD MEMBER [DBAGroup1]
GO -- not necessary since in above Admnistrator and Operator roles
--ALTER ROLE [db_owner] 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
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

Comments

Posted by vijay.ahire8 on 11 November 2013

Hi,

could you please tell me

Can we restrict access to specific database for specific login with sysadmin role?

Posted by vijay.ahire8 on 11 November 2013

Hi,

could you please tell me

Can we restrict access to specific database for specific login with sysadmin role?

Posted by vijay.ahire8 on 11 November 2013

please reply urgent....

Posted by Hugo Shebbeare on 17 January 2014

Once a user is in SysAdmin, you cannot restrict rights, but if you wish to use other Fixed roles to reduce rights, select ServerAdmin, SecurityAdmin, and BackupAdmin or use the Flexible Server roles and assign all the gradual permissions as you can see above.

For a specific login, when you create the login, make the default database the one you want them to restrict them to then assign rights to the User for the Login to that specific dattabase.

Leave a Comment

Please register or log in to leave a comment.