SQL Server 2012 Master Query

10 06 2014

Sorry it took so long to get this query completed. Fo those that have managed to find this article know there is a ton of information in the STIG’s for SQL Server 2012! I ended up rewriting about half of the queries provided in the STIG as they either did not work as is or they could have been implemented better, so now they are! There is alot of duplication of queries going on in this script, I decided to write it this way so that the information returned is in the same order as the STIG is to make matching up everything a little easier. Also please note that this is my first crack at all the code and while it runs, I may have missed something somewhere, please let me know!

Download a docx file with the code (wordpress doesn’t allow .txt)

SQL Server 2012 Master Query Word Document

*** Prior to running the query set your output to text and change the query options for text output to Tab Delimited ***


/* Declare variables used in script */

DECLARE @id Int --Used for traceid loop queries

PRINT 'SQL Server 2012 DB V1R2 STIG Checks'

PRINT 'Rule Title: SQL Server must be protected from unauthorized access by developers.';
PRINT 'STIG ID: SQL2-00-009200'

SELECT name AS 'Account Name'
, create_date AS 'Account Create Date'
, LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'Password Last Set on'
FROM sys.server_principals
WHERE NOT TYPE IN ('C', 'R', 'U') -- ('C', 'G', 'K', 'R', 'S', 'U')
AND NOT name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')
AND sid CONVERT(VARBINARY(85), 0x01) -- no 'sa' account
AND is_disabled 1
ORDER BY name;

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server utilizing Discretionary Access Control (DAC) must enforce a policy that limits propagation of access rights.'
PRINT 'STIG ID: SQL2-00-011050'
Select * from sys.server_permissions
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must provide audit record generation capability for organization-defined auditable events within the database.'
PRINT 'STIG ID: SQL2-00-011200'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')
OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must be monitored to discover unauthorized changes to functions.'
PRINT 'STIG ID: SQL2-00-014900'

PRINT 'This STIG item will require the name of a job that checks for changes to functions.'
PRINT 'use the code below if you know what your job name is'
PRINT '/* Start code block */
DECLARE @Job_title varchar(20)
-- user-defined VARIABLE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
SET @Job_title = ''''
-- user-defined VARIABLE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

EXEC sp_help_job
@job_name = @Job_title
GO
/* end of code block */'

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must be monitored to discover unauthorized changes to triggers.'
PRINT 'STIG ID: SQL2-00-015100'

PRINT 'This STIG item will require the name of a job that checks for changes to functions.'
PRINT 'use the code below if you know what your job name is'
PRINT '/* Start code block */
DECLARE @Job_title varchar(20)
-- user-defined VARIABLE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
SET @Job_title = ''''
-- user-defined VARIABLE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

EXEC sp_help_job
@job_name = @Job_title
GO
/* end of code block */'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must be monitored to discover unauthorized changes to stored procedures.'
PRINT 'STIG ID: SQL2-00-015200'

PRINT 'This STIG item will require the name of a job that checks for changes to functions.'
PRINT 'use the code below if you know what your job name is'
PRINT '/* Start code block */

DECLARE @Job_title varchar(20)
-- user-defined VARIABLE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
SET @Job_title = ''''
-- user-defined VARIABLE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

EXEC sp_help_job
@job_name = @Job_title
GO
/* end of code block */'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must encrypt information stored in the database.'
PRINT 'STIG ID: SQL2-00-019300'

EXEC sp_MSforeachdb '
SELECT ''?'' AS ''database ?'', *
FROM ?.sys.symmetric_keys
ORDER BY name, algorithm_desc
'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must implement required cryptographic protections using cryptographic modules complying with applicable federal laws, Executive Orders, directives, policies, regulations, standards, and guidance.'
PRINT 'STIG ID: SQL2-00-019500'

EXEC sp_MSforeachdb '
SELECT ''?'' AS ''database ?'', *
FROM ?.sys.symmetric_keys
ORDER BY name, algorithm_desc
'

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must employ cryptographic mechanisms preventing the unauthorized disclosure of information at rest, unless the data is otherwise protected by alternative physical measures.'
PRINT 'STIG ID: SQL2-00-021400'
SELECT name AS 'Database Name', is_encrypted AS 'Encryption Status' FROM [master].sys.databases
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must protect against or limit the effects of the organization-defined types of Denial of Service (DoS) attacks.'
PRINT 'STIG ID: SQL2-00-022000'
PRINT '*** CHANGES ARE BEING MADE TO YOUR SYSTEM ***'
PRINT 'Use the below code to configure your system to required STIG settings'

PRINT '/* start code block */
USE MASTER;
EXEC sys.sp_configure N''show advanced options'', N''1'';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N''user connections'';
EXEC sys.sp_configure N''show advanced options'', N''0'';
RECONFIGURE WITH OVERRIDE;
/* end code block */'

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server default account public must be removed from each database.'
PRINT 'STIG ID: SQL2-00-023400'
EXEC sp_MSforeachdb '

IF NOT ''?'' IN (''master'', ''tempdb'', ''model'', ''msdb'')
BEGIN
USE ?
SELECT ''?'' AS ''Database'', su.name AS ''db Account Name'', s.name AS ''SQL Server Account Name''
FROM sys.sysusers AS su
LEFT JOIN sys.server_principals AS s ON su.sid = s.sid
WHERE ( su.name like ''publ%'' OR s.name like ''publ%'') AND NOT su.sid = CONVERT(VARBINARY(85), 0x)
END'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server job/batch queues must be reviewed regularly to detect unauthorized SQL Server job submissions.'
PRINT 'STIG ID: SQL2-00-023500'

SELECT name
FROM master.sys.procedures
WHERE is_auto_executed = 1
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server default account guest must be removed from each database.'
PRINT 'STIG ID: SQL2-00-023800'

EXEC sp_MSforeachdb '
IF NOT ''?'' IN (''master'', ''tempdb'', ''model'', ''msdb'')
BEGIN
USE ?
SELECT ''?'' AS ''Database''
, su.name AS ''db Account Name''
, sp.name AS ''SQL Server Account Name''
FROM sys.sysusers su
LEFT JOIN sys.server_principals sp
ON su.sid = sp.sid
WHERE ( su.name like ''gues%''
OR sp.name like ''gues%'' )
AND NOT su.sid = CONVERT(VARBINARY(85), 0x00)
END '
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: The Database Master Key encryption password must meet DoD password complexity requirements.'
PRINT 'STIG ID: SQL2-00-024000'

EXEC sp_MSforeachdb '
USE ?
SELECT COUNT(name)
FROM sys.symmetric_keys s, sys.key_encryptions k
WHERE s.name = ''##MS_DatabaseMasterKey##''
AND s.symmetric_key_id = k.key_id
AND k.crypt_type = ''ESKP'''
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: The Database Master Key must be encrypted by the Service Master Key where required.'
PRINT 'STIG ID: SQL2-00-024100'
SELECT name
FROM [master].sys.databases
WHERE is_master_key_encrypted_by_server = 1
AND owner_sid 1
AND state = 0
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: Database Master Key passwords must not be stored in credentials within the database.'
PRINT 'STIG ID: SQL2-00-024200'
SELECT COUNT(credential_id)
FROM [master].sys.master_key_passwords
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: Symmetric keys must use a DoD certificate to encrypt the key.'
PRINT 'STIG ID: SQL2-00-024300 '
EXEC sp_MSforeachdb '
USE ?
SELECT s.name, k.crypt_type_desc
FROM sys.symmetric_keys s, sys.key_encryptions k
WHERE s.symmetric_key_id = k.key_id
AND k.crypt_type IN (''KSKP'', ''ESKS'')
AND s.principal_id 1
ORDER BY s.name, k.crypt_type_desc'
PRINT '---------------------------------------------------------------------------------------';
PRINT 'Complete SQL Server 2012 DB V1R2 STIG Checks'
PRINT '';
PRINT '';

PRINT 'SQL Server 2012 Instance V1R2 STIG Checks'
PRINT 'Rule Title: SQL Server must ensure that remote sessions that access an organization-defined list of security functions and security-relevant information are audited.'
PRINT 'STIG ID: SQL2-00-001600'

DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must automatically audit account modification.'
PRINT 'STIG ID: SQL2-00-001900'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce non-DAC policies over users and resources where the policy rule set for each policy specifies access control information (i.e., position, nationality, age, project, time of day).'
PRINT 'STIG ID: SQL2-00-002200'
DECLARE @admin_Account_name sysname
SET @admin_Account_name = 'NO admin ACCOUNT found'
DECLARE @server_name sysname
SET @server_name = 'NO Server found'

SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('K', 'S', 'U')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pr.type
WHEN 'K' THEN 1
WHEN 'S' THEN 2
WHEN 'U' THEN 3
ELSE 4
END

SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('R')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pe.state
WHEN 'D' THEN 1
WHEN 'W' THEN 2
WHEN 'G' THEN 3
ELSE 4
END
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict Alter server state permissions to only authorized roles.'
PRINT 'STIG ID: SQL2-00-002300'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter Server State'

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any event session permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-002400'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any event session'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any event notification permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-002500'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any event notification'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any endpoint permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-002600'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any endpoint'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any database permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-002700'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any database'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any credential permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-002800'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any credential'

PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any connection permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-002900'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any connection'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the View any definition permission.'
PRINT 'STIG ID: SQL2-00-003000'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'view any definition'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any server role permission.'
PRINT 'STIG ID: SQL2-00-003100 '
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any server role'

PRINT '---------------------------------------------------------------------------------------';
PRINT 'Rule Title: SQL Server must not grant users direct access control to the View server state permission.'
PRINT 'STIG ID: SQL2-00-003200'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'view server state'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Create any database permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-003300'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Create any database'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Authenticate server permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-003400'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Authenticate server'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter Settings permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-003500'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter settings'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any server role permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-003600'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any server role'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Create server role permission.'
PRINT 'STIG ID: SQL2-00-003700'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'create server role'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Control server permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-003800'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Control server'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Unsafe assembly permission.'
PRINT 'STIG ID: SQL2-00-003900'

Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Unsafe assembly'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Unsafe assembly permission.'
PRINT 'STIG ID: SQL2-00-003900'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Control Server'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter trace permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-004000'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter trace'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the View server state permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-004100'
Select prin.name as 'Name' from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'View server state'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Shutdown permission.'
PRINT 'STIG ID: SQL2-00-004200'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'shutdown'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any linked server permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-004300'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any linked server'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any login permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-004500'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any logon'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any availability group permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-004600'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any availability group'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any login permission.'
PRINT 'STIG ID: SQL2-00-00470'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any login'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the External access assembly permission.'
PRINT 'STIG ID: SQL2-00-004800'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'External access assembly'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter resources permission.'
PRINT 'STIG ID: SQL2-00-004900'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter resources'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Create trace event notification permission.'
PRINT 'STIG ID: SQL2-00-005000'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Create trace event notification'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter Settings permission.'
PRINT 'STIG ID: SQL2-00-005100'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter Settings'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter trace permission.'
PRINT 'STIG ID: SQL2-00-005200'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter trace'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any event session permission.'
PRINT 'STIG ID: SQL2-00-005300'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any event session'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Connect SQL permission.'
PRINT 'STIG ID: SQL2-00-005400'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Connect SQL'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any linked server permission.'
PRINT 'STIG ID: SQL2-00-005500'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any linked server'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter resources permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-005600'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter resources'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Administer bulk operations permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-005700'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Administer bulk operations'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Control server permission.'
PRINT 'STIG ID: SQL2-00-005800'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Control server'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Create any database permission.'
PRINT 'STIG ID: SQL2-00-005900'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Create any database'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Create availability group permission.'
PRINT 'STIG ID: SQL2-00-006000'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Create availability group'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Create DDL event notification permission.'
PRINT 'STIG ID: SQL2-00-006100'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Create DDL event notification'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Create endpoint permission.'
PRINT 'STIG ID: SQL2-00-006200'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Create endpoint'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Administer bulk operations permission.'
PRINT 'STIG ID: SQL2-00-006300'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Administer bulk operations'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Authenticate server permission.'
PRINT 'STIG ID: SQL2-00-006400 '
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Authenticate server'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the View any definition permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-006500'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'View any definition'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Alter any server audit permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-006600 '
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Alter any server audit'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Create availability group permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-006700'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Create availability group'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Create DDL event notification permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-006800'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Create DDL event notification'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Create endpoint permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-006900'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Create endpoint'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Create server role permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-007000'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Create server role'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Create trace event notification permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-007100'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Create trace event notification'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the External access assembly permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-007200'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'External access assembly'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Shutdown permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-007300'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Shutdown'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any server audit permission.'
PRINT 'STIG ID: SQL2-00-007400'
Select prin.name as 'Name', perm.permission_name from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any server audit'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the View any database permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-007500'
Select prin.name as 'Name', perm.permission_name as 'Permission', perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'View any database'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any event notification permission.'
PRINT 'STIG ID: SQL2-00-007600'
Select prin.name as 'Name', perm.permission_name, perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any event notification'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Connect SQL permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-007700'
Select prin.name as 'Name', perm.permission_name as 'Permission', perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Connect SQL'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter server state permission.'
PRINT 'STIG ID: SQL2-00-007800'
Select prin.name as 'Name', perm.permission_name, perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter server state'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any availability group permission.'
PRINT 'STIG ID: SQL2-00-007900'
Select prin.name as 'Name', perm.permission_name, perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any availability group'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any connection permission.'
PRINT 'STIG ID: SQL2-00-008000'
Select prin.name as 'Name', perm.permission_name, perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any connection'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any credential permission.'
PRINT 'STIG ID: SQL2-00-008100'
Select prin.name as 'Name', perm.permission_name, perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any credential'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any database permission.'
PRINT 'STIG ID: SQL2-00-008200 '
Select prin.name as 'Name', perm.permission_name, perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any database'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must not grant users direct access control to the Alter any endpoint permission.'
PRINT 'STIG ID: SQL2-00-008300'
Select prin.name as 'Name', perm.permission_name, perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where perm.permission_name = 'Alter any endpoint'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce access control policies to restrict the Unsafe assembly permission to only authorized roles.'
PRINT 'STIG ID: SQL2-00-008400'
Select prin.name as 'Name', perm.permission_name as 'Permission', perm.state_desc as 'State'
from sys.server_principals prin
inner join sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id
where prin.type = 'R' AND perm.permission_name = 'Unsafe assembly'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce DAC policy allowing users to specify and control sharing by named individuals, groups of individuals, or by both; limiting propagation of access rights; and including or excluding access to the granularity of a single user.'
PRINT 'STIG ID: SQL2-00-008500'
PRINT 'Check 1 Users'
SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('K', 'S', 'U')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pr.type
WHEN 'K' THEN 1
WHEN 'S' THEN 2
WHEN 'U' THEN 3
ELSE 4
END

PRINT 'Check 2 Roles'
SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('R')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pe.state
WHEN 'D' THEN 1
WHEN 'W' THEN 2
WHEN 'G' THEN 3
ELSE 4
END
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must enforce separation of duties through assigned information access authorizations.'
PRINT 'STIG ID: SQL2-00-008800'
PRINT 'Check 1 Users'
SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('K', 'S', 'U')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pr.type
WHEN 'K' THEN 1
WHEN 'S' THEN 2
WHEN 'U' THEN 3
ELSE 4
END

PRINT 'Check 2 Roles'
SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('R')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pe.state
WHEN 'D' THEN 1
WHEN 'W' THEN 2
WHEN 'G' THEN 3
ELSE 4
END
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must restrict access to sensitive information to authorized user roles.'
PRINT 'STIG ID: SQL2-00-009000'

SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('R')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pe.state
WHEN 'D' THEN 1
WHEN 'W' THEN 2
WHEN 'G' THEN 3
ELSE 4
END
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: Administrators must utilize a separate, distinct administrative account when performing administrative activities, accessing database security functions, or accessing security-relevant information within SQL Server.'
PRINT 'STIG ID: SQL2-00-009600'
SELECT SP1.[name] AS 'Login', 'Role: ' + SP2.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission'
FROM sys.server_principals SP1
JOIN sys.server_role_members SRM
ON SP1.principal_id = SRM.member_principal_id
JOIN sys.server_principals SP2
ON SRM.role_principal_id = SP2.principal_id
UNION ALL
SELECT SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission'
FROM sys.server_principals SP
JOIN sys.server_permissions SPerm
ON SP.principal_id = SPerm.grantee_principal_id
ORDER BY [Login], [ServerPermission]
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server auditing configuration maximum file size must be configured to reduce the likelihood of storage capacity being exceeded, while meeting organization-defined auditing requirements.'
PRINT 'STIG ID: SQL2-00-010400'
Select SUM(max_size) from sys.traces
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server auditing configuration maximum number of files must be configured to reduce the likelihood of storage capacity being exceeded, while meeting organization-defined auditing requirements.'
PRINT 'STIG ID: SQL2-00-010500'
Select SUM(max_size * max_files) from sys.traces
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server utilizing Discretionary Access Control (DAC) must enforce a policy that limits propagation of access rights.'
PRINT 'STIG ID: SQL2-00-011000'
Select * from sys.server_permissions where state_desc != 'GRANT'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server utilizing Discretionary Access Control (DAC) must enforce a policy that includes or excludes access to the granularity of a single user.'
PRINT 'STIG ID: SQL2-00-011100'
PRINT 'Check 1 Users'
SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('K', 'S', 'U')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pr.type
WHEN 'K' THEN 1
WHEN 'S' THEN 2
WHEN 'U' THEN 3
ELSE 4
END

PRINT 'Check 2 Roles'
SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('R')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pe.state
WHEN 'D' THEN 1
WHEN 'W' THEN 2
WHEN 'G' THEN 3
ELSE 4
END
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must generate audit records for the DoD-selected list of auditable events.'
PRINT 'STIG ID: SQL2-00-011400'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must produce audit records containing sufficient information to establish what type of events occurred.'
PRINT 'STIG ID: SQL2-00-011800'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must produce audit records containing sufficient information to establish when (date and time) the events occurred.'
PRINT 'STIG ID: SQL2-00-011900'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must produce audit records containing sufficient information to establish where the events occurred.'
PRINT 'STIG ID: SQL2-00-012000'
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must produce audit records containing sufficient information to establish the sources (origins) of the events.'
PRINT 'STIG ID: SQL2-00-012100'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must produce audit records containing sufficient information to establish the outcome (success or failure) of the events.'
PRINT 'STIG ID: SQL2-00-012200'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must produce audit records containing sufficient information to establish the identity of any user/subject associated with the event.'
PRINT 'STIG ID: SQL2-00-012300'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must include organization-defined additional, more detailed information in the audit records for audit events identified by type, location, or subject.'
PRINT 'STIG ID: SQL2-00-012400'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must shutdown immediately in the event of an audit failure, unless an alternative audit capability exists.'
PRINT 'STIG ID: SQL2-00-012800'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must audit attempts to bypass access controls.'
PRINT 'STIG ID: SQL2-00-013400'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must protect audit information from any type of unauthorized access.'
PRINT 'STIG ID: SQL2-00-013600'
select path from sys.traces
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must protect audit information from unauthorized modification.'
PRINT 'STIG ID: SQL2-00-013700'
select path from sys.traces
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must protect audit information from unauthorized deletion.'
PRINT 'STIG ID: SQL2-00-013800'
select path from sys.traces
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must have the publicly available NorthWind sample database removed.'
PRINT 'STIG ID: SQL2-00-016200'
SELECT name from sysdatabases where name like 'Northwind%'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must have the publicly available AdventureWorks sample database removed.'
PRINT 'STIG ID: SQL2-00-016300'
SELECT name from sysdatabases where name like 'AdventureWorks%'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server default account sa must be disabled.'
PRINT 'STIG ID: SQL2-00-017100'
-- since account was renamed, query will look for account status of sid 0x01 the default sid for sa
Select name, is_disabled as 'Disabled' from sys.sql_logins where sid=0x01
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: Access to xp_cmdshell must be disabled.'
PRINT 'STIG ID: SQL2-00-017200'

Print 'Run the following query to configure your server for this requirement'
PRINT '/* start code block */
EXEC SP_CONFIGURE ''show advanced option'', ''1'';
RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE ''xp_cmdshell'';
/* end code block */'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must specifically prohibit or restrict the use of unauthorized functions and services in each instance.'
PRINT 'STIG ID: SQL2-00-017300'
EXEC sp_MSforeachdb '
DECLARE @nCount integer

SELECT @nCount = Count(*)
FROM ?.sys.objects
WHERE type in (''FN'', ''P'')
AND is_ms_shipped 1

IF @nCount > 0
SELECT ''?'' AS ''Database Name'', *
FROM ?.sys.objects
WHERE type in (''FN'', ''P'')
AND is_ms_shipped 1 '
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must recover to a known state that is verifiable.'
PRINT 'STIG ID: SQL2-00-017500'
EXEC sp_MSforeachdb '
SELECT ''?'' AS ''database name''
, name AS ''log file name''
, physical_name AS ''log file location and name''
, state_desc
, size
, max_size
, growth
, is_percent_growth
FROM ?.sys.database_files
WHERE type_desc = ''LOG''
AND state = 0
'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must have transaction logging enabled.'
PRINT 'STIG ID: SQL2-00-017600'
Select * FROM sys.database_files
WHERE type_desc = 'LOG'
AND state_desc = 'online'
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must uniquely identify and authenticate organizational users (or processes acting on behalf of organizational users).'
PRINT 'STIG ID: SQL2-00-018400'
SELECT name AS 'Account Name'
, CASE is_disabled WHEN 1 THEN 'Yes' ELSE '' END AS 'Is Disabled'
, create_date AS 'Account Create Date'
, LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'Password Last Set on'
FROM sys.server_principals
WHERE NOT TYPE IN ('C', 'R', 'U') -- ('C', 'G', 'K', 'R', 'S', 'U')
AND NOT name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')
AND sid CONVERT(VARBINARY(85), 0x01) -- no 'sa' account
ORDER BY name
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must ensure users are authenticated with an individual authenticator prior to using a group authenticator.'
PRINT 'STIG ID: SQL2-00-018500'
Select uid, name, roles from sys.sysusers
where uid > 16000
and name not in
('db_owner', 'db_accessadmin',
'db_securityadmin', 'db_ddladmin',
'db_backupoperator', 'db_datareader',
'db_datawriter', 'db_denydatareader','db_denydatawriter')
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server default account sa must have its password changed.'
PRINT 'STIG ID: SQL2-00-018800'
SELECT name AS 'Account'
, LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'Time of Last Password Change'
FROM sys.sql_logins
WHERE sid = 0x01
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must protect data at rest and ensure confidentiality and integrity of data.'
PRINT 'STIG ID: SQL2-00-021300'
SELECT name as 'Database Name', is_encrypted as 'Encrypted' FROM [master].sys.databases
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must notify appropriate individuals when accounts are modified.'
PRINT 'STIG ID: SQL2-00-023300'
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'Showing results for Trace ID: ' + CAST(@id as nvarchar(3));
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO(@id)
FETCH NEXT FROM c INTO @id
END
CLOSE c
DEALLOCATE c
PRINT '---------------------------------------------------------------------------------------';

PRINT 'Rule Title: SQL Server must protect against an individual using a group account from falsely denying having performed a particular action.'
PRINT 'STIG ID: SQL2-00-023700'
Select name, principal_id, type_desc from sys.server_principals
PRINT '---------------------------------------------------------------------------------------';

Advertisements




Enable and configure auditing in SQL Server 2008 R2

2 11 2013

Since I mentioned the auditing feature in my prior post I decided to post a quick script to setup and start database auditing in SQL Server 2008 R2.

/* 
Created By: Christopher Provolt
Created: August 2, 2013
Description: Create and intialize database auditing (SQL Server 2008 R2)

**** NOTES ****
Replace ServerName With the name of the server if it is a named instance also include the instance name i.e. ServerName-InstanceName-Audit

Replace Server Audit Name with the name generated for the intial Server Audit object

Make sure to set appropriate file paths to match your environment  */

USE [master] GO

/* Create the initial Server Audit Object */ 
CREATE SERVER AUDIT [ServerName-Audit] TO FILE(FILEPATH = 'E:\Audit\', MAXSIZE=50MB, MAX_ROLLOVER_FILES=200) WITH (QUEUE_DELAY = 1000, ON_FAILURE=CONTINUE) GO

/* Create the Server Audit Specification */  
CREATE SERVER AUDIT SPECIFICATION [ServerName-ServerAudit_Specification] FOR SERVER AUDIT [ServerName-Audit] ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (BACKUP_RESTORE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_ACCESS_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (DATABASE_OPERATION_GROUP), ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (FAILED_LOGIN_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP), ADD (LOGOUT_GROUP), ADD (SCHEMA_OBJECT_ACCESS_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OPERATION_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_STATE_CHANGE_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP), ADD (TRACE_CHANGE_GROUP) GO

/* Enable the Server Audit */ 
ALTER SERVER AUDIT [ServerName-Audit] WITH (STATE = ON)  GO   

 /* Enable the Server Specification Audit */  
ALTER SERVER AUDIT SPECIFICATION [ServerName-ServerAudit_Specification]  WITH (STATE = ON)  GO




Revoke Public / Guest Permissions and tempdb is created from model

2 11 2013

Probably the worst and maybe most important step to STIGing a SQL Server is to remove public and guest permissions, I found a great script over at Fort SQL, http://blogs.technet.com/b/fort_sql/archive/2010/02/04/remove-public-and-guest-permissions.aspx, to perform this action. I would suggest if using this script first ensure that you run it as script only, there is a variable that can be set. This will allow you to roll back any all of the changes that get made when the script is ran in execute mode.

If you use this script it will break your applications that use the SQL Server! It is not a question of if but more of how badly! I recommend that prior to running this script you enable the built-in auditing features of MSSQL Server 2008, as the information collected is invaluable to resolving the permissions issues that will occur.

That being said I ran this script against a database server that hosted the database for SCCM 2007. I knew that SCCM was going to break and was very prepared to deal with that. It wasn’t until several days later that the SQL Server was rebooted and SCCM stopped deploying patches. Looking at the failures in the audit log I quickly realized that guest required the select permissions to sys.sysobjects in the tempdb.

use tempdb
grant select on sys.sysobjects to guest;

executing this resolved the issue and SCCM happily began chugging away with deploying software!

This resolution only worked as long as the SQL Server did not shutdown, as the tempdb is re-created at every startup.  Looking for solutions to this issue many places I looked advised to just create a job to run using the SQL Agent service everytime it started. This seemed like ghetto fix for the problem, what happens when that service fails to start or the script fails to execute? I know the answer is I’m going to have to manually update permissions again! After some more research I found that tempdb is created in the eyes of the model and if I set the permissions in the model database they would translate to the tempdb upon reboot.

use model
grant select on sys.sysobjects to guest;

The problem is now resolved and no longer or will ever require intervention from me.

 





SQL Server STIG – The Master Query

10 07 2013

I was recently tasked to apply the DISA Security Technical Implementation Guidelines (STIG) to all the Microsoft SQL Servers in our environment. This alone is a huge undertaking and to make matters worse the STIG’s for SQL Server have not been updated by DISA since the release of SQL Server 2005 STIG. Since there are multiple Microsoft SQL Servers in our environment a key to getting this job done is to automate as many as the tasks that are required as part of the STIG requirements. The first thing any database administrator applying the STIG to current versions of SQL Server is that there are alot of required queries to be ran against the database server. Typically the queries are copied out of the PDF format the STIG comes in and pasted into the SQL Server Management Studio (SSMS). Doing this once was more than I ever wanted to do in the first place, so I created what I call the Master Query which contains the vast majority of the required queries to be ran during the STIG process.

The query does not make any modifications to the SQL Server

/*
v 1.0
Master SQL Server STIG Query
Based off of SQL Server 2005 STIG

Created: 9 July 2013
Author: Christopher Provolt

Please change output to Text (Ctrl + T) or Query > Results To > Results To Text
Change output to be Tab delimited as opposed to column delimited.
*/


DECLARE @database VARCHAR(255)
DECLARE @cmd varchar(1000)


PRINT 'List all active databases'
SELECT name from master.sys.databases WHERE state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002424 / DG0060 DBMS shared account authorization'
SELECT name
FROM [master].sys.server_principals
WHERE type IN ('S', 'U')
AND sid <> 0x01
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002426 / DM0510 C2 audit mode'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'c2 audit mode'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002427 / DM0530 Fixed server role members'
EXEC SP_HELPSRVROLEMEMBER 'bulkadmin'
EXEC SP_HELPSRVROLEMEMBER 'dbcreator'
EXEC SP_HELPSRVROLEMEMBER 'diskadmin'
EXEC SP_HELPSRVROLEMEMBER 'processadmin'
EXEC SP_HELPSRVROLEMEMBER 'securityadmin'
EXEC SP_HELPSRVROLEMEMBER 'serveradmin'
EXEC SP_HELPSRVROLEMEMBER 'setupadmin'
EXEC SP_HELPSRVROLEMEMBER 'sysadmin'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002436 / DM0660 MS SQL Server instance name'
SELECT RTRIM(CONVERT(CHAR(20), SERVERPROPERTY('instancename')))
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002451 / DM1709 Guest user'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE name NOT IN ('master', 'temp') AND state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+'];
SELECT COUNT(grantee_principal_id)
FROM sys.database_permissions
WHERE grantee_principal_id = 2
AND state = ''G''
AND permission_name = ''CONNECT'''

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002457 / DM1715 Unauthorized object permission grants'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Type]
FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.state IN (''G'', ''W'')
AND (p.type NOT IN (''DL'', ''EX'', ''IN'', ''SL'', ''UP'')
OR u.name IN (''public'', ''guest''))
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002458 / DM1749 System table permissions'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], t.name AS [Table], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
JOIN sys.tables t ON p.major_id = t.object_id
WHERE p.state_desc <> ''DENY''
AND t.is_ms_shipped = 1
ORDER BY u.name, t.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002461 / DM1758 xp_cmdshell option Query 1'
SELECT u.name
FROM [master].dbo.sysobjects o, [master].dbo.sysusers u, [master].dbo.sysprotects p
WHERE p.uid = u.uid
AND p.id = o.id
AND o.name = 'xp_cmdshell'
ORDER BY u.name
PRINT '<---------------------------------END------------------------------------>'

PRINT 'V0002461 / DM1758 xp_cmdshell option Query 2'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'xp_cmdshell'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002463 / DM1760 DDL permission assignments'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8)
LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE p.state_desc <> ''DENY''
AND p.state IN (''G'', ''W'')
AND (p.type LIKE ''CR%'' OR p.type LIKE ''AL%'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002464 / DM1761 Scan for startup stored procedures option'
PRINT 'Config_Value should be a value of 0'
PRINT 'Config_Value should be a value of 1 if custom trace auditing is enabled'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'scan for startup procs'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002472 / DM2095 OLE automation procedures option Step 1'
PRINT 'Config_Value should be a value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'ole automation procedures'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002472 / DM2095 OLE automation procedures option Step 2'
SELECT USER_NAME(p.grantee_principal_id) 'User', o.name 'Object', p.permission_name 'Perm'
FROM [master].sys.system_objects o, [master].sys.database_permissions p
WHERE o.object_id = p.major_id
AND o.name like 'sp_OA%'
ORDER BY USER_NAME(p.grantee_principal_id), o.name, p.permission_name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002473 / DM2119 Registry extended stored procedures access'
SELECT u.name AS [Principal], o.name AS [Extended Stored Procedure], u.type_desc AS [Type]
FROM [master].sys.system_objects o
JOIN [master].sys.database_permissions p ON o.object_id = p.major_id
JOIN [master].sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE ( o.name LIKE 'xp_reg%' OR o.name LIKE 'xp_instance_reg%')
AND p.type = 'EX'
ORDER BY o.name, u.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002485 / DM2142 Remote access option'
PRINT 'Config_Value should be a value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'remote access'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002487 / DM3566 Authentication mode' 
EXEC XP_LOGINCONFIG 'login mode'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002488 / DM3763 CmdExec or ActiveScripting jobs'
USE msdb
EXEC SP_ENUM_PROXY_FOR_SUBSYSTEM @subsystem_name = 'ActiveScripting'
EXEC SP_ENUM_PROXY_FOR_SUBSYSTEM @subsystem_name = 'CmdExec'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002498 / DM5144 WITH GRANT privilege assignments'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 

SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8)
LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE p.state_desc <> ''DENY''
AND p.state IN (''W'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002508 / DG0070 DBMS user account authorization'
SELECT name
FROM sys.server_principals
WHERE type IN ('S', 'U')
AND principal_id <> 1
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003335 / DM0900 SQL and database mail use Query 1'
PRINT 'Expected value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'sql mail xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003335 / DM0900 SQL and database mail use Query 2'
PRINT 'Expected value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'database mail xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003727 / DG0015 DBMS data definition language use'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT s.name AS [Schema], o.name AS [Object], u.type_desc AS [TYPE], o.create_date AS
[Create Date]
FROM sys.schemas s
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN sys.all_objects o ON s.schema_id = o.schema_id
WHERE u.name NOT IN (''dbo'', ''INFORMATION_SCHEMA'', ''sys'')
AND o.is_ms_shipped = 0
ORDER BY s.name, o.name, o.create_date'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003808 / DG0051 Database job/batch queue monitoring Step 1'
SELECT name FROM [master].sys.procedures WHERE is_auto_executed = 1
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003808 / DG0051 Database job/batch queue monitoring Step 2'
SELECT distinct j.name from msdb.dbo.sysjobhistory h, msdb.dbo.sysjobs j WHERE h.job_id = j.job_id
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003823 / DG0091 DBMS source code encoding or encryption'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT USER_NAME(o.schema_id) AS [Schema], o.name AS [Object], o.type_desc AS [Type],
o.create_date AS [Create Date]
FROM sys.all_objects o, sys.sql_modules s
WHERE o.object_id = s.object_id
AND s.definition IS NOT NULL
AND o.is_ms_shipped = 0
ORDER BY USER_NAME(o.schema_id), o.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0005683 / DG0004 DBMS application object owner accounts'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 

SELECT DISTINCT s.name AS [Schema], l.name AS [User]
FROM sys.all_objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN master.sys.sql_logins l ON u.sid = l.sid
WHERE o.is_ms_shipped = 0
AND l.is_disabled = 0'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0005686 / DG0032 DBMS audit record access'
SELECT u.name AS [User], o.name AS [Object], p.permission_name AS [Permission]
FROM [master].sys.all_objects o, [master].sys.database_principals u,
[master].sys.database_permissions p
WHERE p.grantee_principal_id = u.principal_id
AND o.object_id = p.major_id
AND (o.name = 'dm_exec_sessions' OR o.name = 'sysprotects')
ORDER BY u.name, o.name, p.permission_name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015105 / DG0120 DBMS application user access to external objects'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], o.name AS [External Procedure Name], p.permission_name AS
[Permission], p.state_desc AS [State]
FROM master.sys.all_objects o
JOIN master.sys.database_permissions p ON p.major_id = o.object_id
JOIN master.sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE o.type = ''X''
ORDER BY o.name, u.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015107 / DG0063 DBMS restore permissions Step 1'
SELECT p.name 'User', r.name 'Role'
FROM [master].sys.server_principals p, [master].sys.server_principals r,
[master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 9
AND m.member_principal_id <> 1
ORDER BY r.name, p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015107 / DG0063 DBMS restore permissions Step 2'
SELECT p.name 'User', r.name 'Role'
FROM [master].sys.server_principals p, [master].sys.server_principals r,
[master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 3
AND m.member_principal_id <> 1
ORDER BY r.name, p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015107 / DG0063 DBMS restore permissions Step 3'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT p.name ''User'', r.name ''Role''
FROM sys.database_principals p, sys.database_principals r, sys.database_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 16384
ORDER BY r.name, p.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015113 / DM6065 SQL Server replication agent accounts'
PRINT 'No results expected'
SELECT c.credential_identity, p.name
FROM [master].sys.credentials c, [msdb].dbo.sysproxies p, [msdb].dbo.sysproxysubsystem s
WHERE c.credential_id = p.credential_id
AND s.proxy_id = p.proxy_id
AND s.subsystem_id > 3
AND s.subsystem_id < 9
ORDER BY c.credential_identity, p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015125 / DM6045 SQL Server Agent permissions to proxies.'
PRINT 'No results expected'
USE msdb
EXEC sp_enum_proxy_for_subsystem
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015128 / DG0105 DBMS application user role privilege assignment'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+'];
SELECT r.name, o.name, p.permission_name
FROM sys.database_principals r, sys.database_permissions p, sys.all_objects o
WHERE p.grantee_principal_id = r.principal_id
AND p.major_id = o.object_id
AND r.principal_id NOT IN (0, 2)
AND r.type IN (''A'', ''R'')
AND r.is_fixed_role = 0
ORDER BY r.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015130 / DG0074 DBMS inactive accounts'
SELECT SUBSTRING(name, 1, 30) AS [LoginName],
CASE is_disabled WHEN 1 THEN 'true' ELSE 'false' END AS [IsDisabled],
CASE CAST(LoginProperty(name, 'IsExpired') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS
[IsExpired],
CASE CAST(LoginProperty(name, 'IsLocked') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS
[IsLocked]
FROM [master].sys.server_principals
WHERE type = 'S'
AND (is_disabled <> 1
AND CAST(LoginProperty(name, 'IsExpired') AS int) = 1
OR CAST(LoginProperty(name, 'IsLocked') AS int) = 1)
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015151 / DM0531 Fixed database role members'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT u.name AS [Principal], g.name AS [Role]
FROM sys.database_role_members r, sys.database_principals u, sys.database_principals g
WHERE r.role_principal_id = g.principal_id
AND r.member_principal_id = u.principal_id
AND g.is_fixed_role = 1
ORDER BY u.name, g.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015152	/ DG0079 DBMS password complexity'
SELECT name
FROM [master].sys.sql_logins
WHERE type = 'S'
AND is_policy_checked <> '1'
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015153 / DG0125 DBMS account password expiration'
SELECT name
FROM [master].sys.sql_logins
WHERE type = 'S'
AND is_expiration_checked <> '1'
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015154 / DG0190 DBMS remote system credential use and access'
PRINT 'Query should return 0 rows'
SELECT s.name AS [Local Link Server Name], SUSER_NAME(l.local_principal_id) AS [Server
Principal], l.remote_name AS [Remote User Name]
FROM [master].sys.servers s
JOIN [master].sys.linked_logins l ON s.server_id = l.server_id
WHERE l.server_id <> 0
ORDER BY l.server_id
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015159 / DM6175 Database Master key encryption password'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT COUNT(name)
FROM sys.symmetric_keys s, sys.key_encryptions k
WHERE s.name = ''##MS_DatabaseMasterKey##''
AND s.symmetric_key_id = k.key_id
AND k.crypt_type = ''ESKP'''

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015161 / DM6179 database master key encrypted by server'
PRINT 'No results should be returned'
SELECT name
FROM [master].sys.databases
WHERE is_master_key_encrypted_by_server = 1
AND owner_sid <> 1
AND state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015162 / DM6180 Database Master key password storage'
SELECT COUNT(credential_id)
FROM [master].sys.master_key_passwords
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015164 / DM6184 Asymmetric keys specify DoD PKI'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT name, SUSER_SNAME(sid)
FROM sys.asymmetric_keys
ORDER BY name, SUSER_SNAME(sid)'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015165 / DM6128 service broker access'
PRINT 'This query should return no results'
SELECT name FROM [master].sys.service_broker_endpoints
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015166 / DM6150 ad hoc distributed queries option'
PRINT 'Config_Value should be a value of 0 '
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'ad hoc distributed queries'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015167 / DM6189 Dedicated data file directories Step 1'
SELECT physical_name, type_desc
FROM [master].sys.master_files
ORDER BY physical_name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015167 / DM6189 Dedicated data file directories Step 2'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT physical_name, type_desc
FROM sys.database_files
ORDER BY physical_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015168 / DM6183 Symmetric keys encrypting mechanism'
PRINT 'No results should be returned'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
SET @cmd = 'USE ['+@database+'];
SELECT s.name, k.crypt_type_desc
FROM sys.symmetric_keys s, sys.key_encryptions k
WHERE s.symmetric_key_id = k.key_id
AND k.crypt_type IN (''KSKP'', ''ESKS'')
AND s.principal_id <> 1
ORDER BY s.name, k.crypt_type_desc'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015172 / DM6196 DBMS object permission grants to PUBLIC or Guest'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
JOIN sys.all_objects o ON p.major_id = o.object_id
WHERE p.state_desc <> ''DENY''
AND u.name IN (''guest'', ''public'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015173 / DM6189 Database TRUSTWORTHY status'
PRINT 'This query should return no results'
SELECT name
FROM [master].sys.databases
WHERE is_trustworthy_on = 1
AND name <> 'msdb'
AND state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015178 / DM6070 Replication administration role privileges'
PRINT 'Config_Value should be a value of 0'
SELECT COUNT(name)
FROM [master].sys.databases
WHERE name = 'distribution'
AND state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015185 / DM6185 Asymmetric keys private key encryption type'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT name, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys
WHERE pvt_key_encryption_type = ''PW''
ORDER BY name, pvt_key_encryption_type_desc'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015196 / DM6145 Proxy account subsystem privileges'
SELECT p.name, sp.subsystem
FROM [msdb].dbo.sysproxies p, [msdb].dbo.sysproxysubsystem s, [msdb].dbo.syssubsystems sp
WHERE p.proxy_id = s.proxy_id
AND s.subsystem_id = sp.subsystem_id
ORDER BY p.name, sp.subsystem
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015197 / DM6140 SQL Server Agent dedicated proxy accounts'
SELECT c.name AS [Credential], c.credential_identity AS [Identity], p.name AS [Proxy],
b.subsystem AS [Subsystem]
FROM [master].sys.credentials c
JOIN [msdb].dbo.sysproxies p ON c.credential_id = p.credential_id
JOIN [msdb].dbo.sysproxysubsystem s ON s.proxy_id = p.proxy_id
JOIN [msdb].dbo.syssubsystems b ON s.subsystem_id = b.subsystem_id
JOIN (SELECT w.credential_identity
FROM [master].sys.credentials w
JOIN [msdb].dbo.sysproxies x ON w.credential_id = x.credential_id
JOIN [msdb].dbo.sysproxysubsystem y ON x.proxy_id = y.proxy_id
WHERE (y.subsystem_id < 4 OR y.subsystem_id > 8)
GROUP BY w.credential_identity HAVING COUNT(*) > 1) d
ON c.credential_identity = d.credential_identity
WHERE (s.subsystem_id < 4 OR s.subsystem_id > 8)
ORDER BY c.name, p.name, b.subsystem
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015198 / DM6130 Web assistance procedures option'
PRINT 'Config_Value should be a value of 0 or no results'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'web assistant procedures'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015201 / DM6130 Cross db ownership chaining option'
PRINT 'Config_Value should be a value of 0 '
SELECT CAST(value AS INT) AS [Config Value]
FROM [master].sys.configurations
WHERE name = 'cross db ownership chaining'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015202 / DM6123 clr_enabled parameter'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'clr enabled'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015206 / DM6126 XML web service access'
PRINT 'This query should return no results'
SELECT name
FROM [master].sys.http_endpoints
WHERE (is_integrated_auth_enabled = 0
AND is_kerberos_auth_enabled = 0
AND is_ntlm_auth_enabled = 0)
AND state = 0
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015210 / DM6198 Agent XPs option'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'agent xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015211 / DM6199 SMO and DMO XPs option'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'smo and dmo xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015607 / DG0008 DBMS application object ownership'
DECLARE DatabaseCursor CURSOR FOR
SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT DISTINCT s.name AS [Schema Name], u.name AS [Principal], u.type_desc AS [Type]
FROM sys.schemas s
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN sys.all_objects o ON s.schema_id = o.schema_id
WHERE u.name NOT IN (''dbo'', ''INFORMATION_SCHEMA'', ''sys'')
ORDER BY s.name, u.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015610 / DG0025 DBMS encryption compliance'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT name, algorithm_desc FROM sys.symmetric_keys
WHERE key_algorithm NOT IN (''D3'',''A1'',''A2'',''A3'')
ORDER BY name, algorithm_desc'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015614 / DG0084 DBMS residual data clearance'
SELECT CAST(value AS INT)
FROM [master].sys.configurations
WHERE name = 'common criteria compliance enabled'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015618 / DG0099 DBMS access to external local executables'
SELECT o.name AS [External Procedure Name]
FROM master.sys.all_objects o
LEFT JOIN master.sys.database_permissions p ON p.major_id = o.object_id
LEFT JOIN master.sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE o.type = 'X' AND u.name IS NULL
ORDER BY o.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015626 / DG0116 DBMS privileged role assignments'
SELECT p.name
FROM [master].sys.server_principals p, [master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND m.member_principal_id <> 1
AND m.role_principal_id = 3
ORDER BY p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015629 / DG0121 DBMS application user privilege assignment'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT u.name, o.name, p.permission_name
FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.state IN (''G'', ''W'')
AND u.type IN (''S'', ''U'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015635 / DG0128 DBMS default passwords' 
SELECT name AS [User], type_desc AS [Type], create_date AS [Create Date]
FROM [master].sys.sql_logins
WHERE PWDCOMPARE ('', password_hash) = 1
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015638 / DG0131 DBMS default account names'
SELECT name
FROM [master].sys.sql_logins
WHERE name = 'sa'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015644 / DG0141 DBMS access control bypass'
EXEC XP_LOGINCONFIG 'audit level'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015645 / DG0142 DBMS privileged action audit'
PRINT 'Config_Value should be a value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'default trace enabled'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015651 / DG0157 DBMS remote administration'
PRINT 'Config_Value should be a valus of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'remote admin connections'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015654 / DG0165 DBMS symmetric key management'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT USER_NAME(grantee_principal_id)
FROM sys.database_permissions
WHERE class = 0
AND state IN (''G'', ''W'')
AND type = ''CL''
ORDER BY USER_NAME(grantee_principal_id)'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'