Technical Article

Security Queries: Server-level

,

This script contains various queries I have written on the subject of security as it applies to a single SQL Server instance. It's arranged so I can either (A) run one query I need alone or (B) run the whole thing as a batch to dump out everything at once.

Option B might not seem useful at first (information overload!), but consider that if your output in SSMS is set to "Results to Text", this will allow you to quickly copy off a single text file that contains everything you might want to know about that server's security at a point in time. Furthermore, the queries are carefully structured and ordered to be consistent. This makes two of these text files easy to compare with a standard compare/diff/merge application (e.g. WinMerge, Beyond Compare).

Included queries:

  • Server information & settings
  • Server roles
  • Server logins
  • Server role membership
  • Server role and login permissions
  • Server-level SQL/Assembly module EXECUTE AS
  • Database ownership
  • SQL Agent Job ownership
  • SQL Agent Schedule ownership
  • SSIS package ownership
  • Maintenance plan ownership

Please also see related script Security Queries: Database-level.

/* 
 * Security Queries: Server-level
 * Released on 2013-10-24 by Greg Drake
 * Compatible with SQL Server 2005+
 * 
 * This set of queries attempts to gather as much security-related information on a single server as possible.
 * The output for any of these should be the same regardless of the currently selected database.
 * How to use this script to compare multiple servers or environments...
 *     (1) Set "Results to text"
 *     (2) Execute the entire script as a single batch.
 *     (3) Save results in text files 
 *     (4) Use any common text file comparison/merge application (e.g. WinMerge, Beyond Compare)
 * Note that many result sets have columns commented out to make comparison easier. These columns (mostly dates) will always be different and generally aren't that useful.
 */

-- Runtime context
PRINT 'Executed by ' + quotename(suser_sname()) + ' on server ' + quotename(@@servername) + ' at ' + convert(varchar(50), getdate(), 120) + '.' + (char(13) + char(10)) + (char(13) + char(10))


PRINT 'SERVER INFORMATION & SETTINGS'
SELECT
[item]
,[value]
FROM
(
(SELECT           [order] = 10, [item] = convert(nvarchar(30), 'authentication_mode'), [value] = convert(nvarchar(128), CASE serverproperty('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication mode' WHEN 0 THEN 'SQL Server and Windows Authentication mode (mixed mode)' END))
UNION ALL (SELECT [order] = 15, [item] = convert(nvarchar(30), 'single-user_mode'   ), [value] = convert(nvarchar(128), serverproperty('IsSingleUser')))
UNION ALL (SELECT [order] = 20, [item] = convert(nvarchar(30), [name]               ), [value] = convert(nvarchar(128), value_in_use) FROM sys.configurations WHERE [name] = N'remote access')
UNION ALL (SELECT [order] = 25, [item] = convert(nvarchar(30), [name]               ), [value] = convert(nvarchar(128), value_in_use) FROM sys.configurations WHERE [name] = N'remote login timeout (s)')
UNION ALL (SELECT [order] = 30, [item] = convert(nvarchar(30), [name]               ), [value] = convert(nvarchar(128), value_in_use) FROM sys.configurations WHERE [name] = N'remote admin connections')
UNION ALL (SELECT [order] = 35, [item] = convert(nvarchar(30), [name]               ), [value] = convert(nvarchar(128), value_in_use) FROM sys.configurations WHERE [name] = N'clr enabled')
UNION ALL (SELECT [order] = 40, [item] = convert(nvarchar(30), [name]               ), [value] = convert(nvarchar(128), value_in_use) FROM sys.configurations WHERE [name] = N'xp_cmdshell')
UNION ALL (SELECT [order] = 45, [item] = convert(nvarchar(30), [name]               ), [value] = convert(nvarchar(128), value_in_use) FROM sys.configurations WHERE [name] = N'c2 audit mode')
UNION ALL (SELECT [order] = 50, [item] = convert(nvarchar(30), [name]               ), [value] = convert(nvarchar(128), value_in_use) FROM sys.configurations WHERE [name] = N'cross db ownership chaining')
) ugly_union_derived_table
ORDER BY
[order]


PRINT 'SERVER ROLES'
SELECT
'role_name' = ssp_r.[name]
,ssp_r.is_disabled
--,ssp_r.create_date
--,ssp_r.modify_date
FROM
sys.server_principals ssp_r
WHERE
ssp_r.[type] = 'R'
ORDER BY
ssp_r.[name]


PRINT 'SERVER LOGINS'
SELECT
'login_type'  = ssp_l.type_desc
,'login_name' = ssp_l.[name]
,ssp_l.is_disabled
--,ssp_l.create_date
--,ssp_l.modify_date
,ssp_l.default_database_name
,ssp_l.default_language_name
,sslog.is_policy_checked
,sslog.is_expiration_checked
FROM
sys.server_principals ssp_l
LEFT OUTER JOIN
sys.sql_logins sslog ON (ssp_l.principal_id = sslog.principal_id)
WHERE
-- Constraints taken from SQL Server Management Studio > Object Explorer
(ssp_l.[type] IN ('U', 'G', 'S', 'C', 'K')
AND ssp_l.principal_id NOT BETWEEN 101 AND 255
AND ssp_l.[name] <> N'##MS_AgentSigningCertificate##')
ORDER BY
ssp_l.type_desc
,ssp_l.[name]


PRINT 'SERVER ROLE MEMBERSHIP'
SELECT
'role_name'    = ssp_r.[name]
,'member_type' = isnull(ssp_l.type_desc, N'<NO MEMBERS>')
,'member_name' = isnull(ssp_l.[name], N'')
FROM
sys.server_principals ssp_r
LEFT OUTER JOIN
(
sys.server_principals ssp_l
LEFT OUTER JOIN
sys.server_role_members ssrm ON (ssp_l.principal_id = ssrm.member_principal_id)
) ON (
ssp_r.principal_id = ssrm.role_principal_id
AND (
-- A role can be a member of another role, so this query is general in terms of the member
ssp_l.[type] = 'R'
OR (
-- Constraints taken from SQL Server Management Studio > Object Explorer
ssp_l.[type] IN ('U', 'G', 'S', 'C', 'K')
AND ssp_l.principal_id NOT BETWEEN 101 AND 255
AND ssp_l.[name] <> N'##MS_AgentSigningCertificate##'
)
)
)
WHERE
ssp_r.[type] = 'R'
ORDER BY
ssp_r.[name]
,ssp_l.type_desc
,ssp_l.[name]


PRINT 'SERVER ROLE AND LOGIN PERMISSIONS'
SELECT
'principal_type'   = ssp.type_desc
,'principal_name'  = ssp.[name]
,'class'           = isnull(ssper.class_desc, N'<NO_SECURABLES>')
,'object_name'     = isnull(convert(nvarchar(128), CASE
WHEN (ssper.class = 100) /* Server           */ THEN serverproperty(N'Servername')
WHEN (ssper.class = 101) /* Server-principal */ THEN (SELECT lookup_ssp.[name] FROM sys.server_principals lookup_ssp WHERE lookup_ssp.principal_id = ssper.major_id)
WHEN (ssper.class = 105) /* Endpoint         */ THEN (SELECT lookup_sep.[name] FROM sys.endpoints lookup_sep WHERE lookup_sep.endpoint_id = ssper.major_id)
ELSE (N'<UNHANDLED_LOOKUP class=' + convert(nvarchar(20), ssper.class) + N', major_id=' + convert(nvarchar(20), ssper.major_id) + N', minor_id=' + convert(nvarchar(20), ssper.minor_id) + N'>')
END), N'')
,'permission_name' = isnull(ssper.permission_name, N'')
,'state'           = isnull(ssper.state_desc, N'')
--,[granted_by]      = (SELECT ssp2.[name] FROM sys.server_principals ssp2 WHERE ssp2.principal_id = ssper.grantor_principal_id)
FROM
sys.server_principals AS ssp
LEFT OUTER JOIN
sys.server_permissions AS ssper ON (ssp.principal_id = ssper.grantee_principal_id)
WHERE
ssp.[type] = 'R'
OR (
-- Constraints taken from SQL Server Management Studio > Object Explorer
ssp.[type] IN ('U', 'G', 'S', 'C', 'K')
AND ssp.principal_id NOT BETWEEN 101 AND 255
AND ssp.[name] <> N'##MS_AgentSigningCertificate##'
)
ORDER BY
ssp.type_desc
,ssp.[name]
,CASE
WHEN (ssper.class = 100) /* Server           */ THEN convert(sysname, serverproperty(N'Servername'))
WHEN (ssper.class = 101) /* Server-principal */ THEN (SELECT lookup_ssp.[name] FROM sys.server_principals lookup_ssp WHERE lookup_ssp.principal_id = ssper.major_id)
WHEN (ssper.class = 105) /* Endpoint         */ THEN (SELECT lookup_sep.[name] FROM sys.endpoints lookup_sep WHERE lookup_sep.endpoint_id = ssper.major_id)
ELSE NULL
END
,ssper.permission_name
,ssper.state_desc


PRINT 'SERVER-LEVEL SQL/ASSEMBLY MODULE EXECUTE AS'
-- TODO: Test this query with better data
SELECT
'object_type'                = coalesce(ssl_sql.type_desc, ssl_ass.type_desc)
,'object_name'               = coalesce(ssl_sql.[name], ssl_ass.[name])
,'execute_as_principal_type' = CASE WHEN coalesce(sssm.execute_as_principal_id, ssam.execute_as_principal_id) = -2 THEN N'OWNER' ELSE ssp.type_desc END
,'execute_as_principal'      = ssp.name
FROM
(
sys.server_triggers ssl_sql
INNER JOIN
sys.server_sql_modules sssm ON (ssl_sql.[object_id] = sssm.[object_id])
)
FULL OUTER JOIN
(
sys.server_triggers ssl_ass
INNER JOIN
sys.server_assembly_modules ssam ON (ssl_ass.[object_id] = ssam.[object_id])
) ON (ssl_sql .[object_id] = ssl_ass.[object_id])
LEFT OUTER JOIN
sys.server_principals ssp ON (coalesce(sssm.execute_as_principal_id, ssam.execute_as_principal_id) = ssp.principal_id)
WHERE
sssm.execute_as_principal_id IS NOT NULL
OR ssam.execute_as_principal_id IS NOT NULL
ORDER BY
coalesce(ssl_sql.type_desc, ssl_ass.type_desc)
,coalesce(ssl_sql.[name], ssl_ass.[name])
,CASE WHEN coalesce(sssm.execute_as_principal_id, ssam.execute_as_principal_id) = -2 THEN N'OWNER' ELSE ssp.type_desc END
,ssp.name


PRINT 'DATABASE OWNERSHIP'
SELECT
'database_name' = sd.[name]
,'owner_type'   = ssp.type_desc
,'owner_name'   = ssp.[name]
FROM
sys.databases sd
LEFT OUTER JOIN
sys.server_principals ssp ON (sd.owner_sid = ssp.[sid])
ORDER BY
sd.[name]


PRINT 'SQL AGENT JOB OWNERSHIP'
SELECT
'job_name'    = mdsj.[name]
,'owner_type' = ssp.type_desc
,'owner_name' = ssp.[name]
FROM
msdb.dbo.sysjobs mdsj
LEFT OUTER JOIN
sys.server_principals ssp ON (mdsj.owner_sid = ssp.[sid])
ORDER BY
mdsj.[name]


PRINT 'SQL AGENT SCHEDULE OWNERSHIP'
SELECT
'schedule_name' = mdss.[name]
,'owner_type'   = ssp.type_desc
,'owner_name'   = ssp.[name]
FROM
msdb.dbo.sysschedules mdss
LEFT OUTER JOIN
sys.server_principals ssp ON (mdss.owner_sid = ssp.[sid])
ORDER BY
mdss.[name]


PRINT 'SSIS PACKAGE OWNERSHIP'
IF ((SELECT [compatibility_level] FROM sys.databases WHERE [name] = N'msdb') = 90)
BEGIN
SELECT
'package_name' = mdsssisp.[name]
,'owner_type'  = ssp.type_desc
,'owner_name'  = ssp.[name]
FROM
msdb.dbo.sysdtspackages90 mdsssisp
LEFT OUTER JOIN
sys.server_principals ssp ON (mdsssisp.ownersid = ssp.[sid])
ORDER BY
mdsssisp.[name]
END
ELSE
BEGIN
SELECT
'package_name' = mdsssisp.[name]
,'owner_type'  = ssp.type_desc
,'owner_name'  = ssp.[name]
FROM
msdb.dbo.sysssispackages mdsssisp
LEFT OUTER JOIN
sys.server_principals ssp ON (mdsssisp.ownersid = ssp.[sid])
ORDER BY
mdsssisp.[name]
END


PRINT 'MAINTENANCE PLAN OWNERSHIP'
SELECT
'plan_name'   = mdsdmp.plan_name
,'owner_type' = isnull(ssp.type_desc, 'NOT_FOUND')
,'owner_name' = mdsdmp.[owner]
FROM
msdb.dbo.sysdbmaintplans mdsdmp
LEFT OUTER JOIN
sys.server_principals ssp ON (mdsdmp.[owner] = ssp.[name])
ORDER BY
mdsdmp.plan_name

Read 3,741 times
(3 in last 30 days)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.33 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.33 (3)

You rated this post out of 5. Change rating

Related content

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy