SQL Server
Solutions
Example Queries
Get Config Settings
SELECT *
FROM sys.configurations
Select Into Variable
USE master;
GO
DECLARE @TblTmp TABLE ([name] nvarchar(100), is_disabled bit)
INSERT INTO @TblTmp ([name], is_disabled)
SELECT [name], is_disabled
FROM sys.sql_logins
WHERE principal_id = 1
SELECT * FROM @TblTmp
USE [master]
GO
DECLARE @IsFinding bit
SET @IsFinding = 0
IF EXISTS (SELECT [name]
FROM sys.databases
WHERE [name] IN ('pubs', 'Northwind', 'AdventureWorks', 'WorldwideImporters'))
SET @IsFinding = 1
IF @IsFinding = 1
SELECT @@SERVERNAME AS [Server Name], 'Finding' AS STATUS, 'STIG V-213954' AS reference
ELSE
SELECT @@SERVERNAME AS [Server Name], 'Pass' AS STATUS, 'STIG V-213954' AS reference
Execute Query Against Every Database
USE [master]
GO
IF object_id('tempdb.dbo.#database') IS NOT NULL
DROP TABLE #database
GO
CREATE TABLE #database(id INT IDENTITY PRIMARY KEY, name sysname)
GO
SET nocount ON
INSERT INTO #database(name)
SELECT name
FROM sys.databases
WHERE name LIKE '%tgsdb%' --CHANGE HERE THE FILTERING RULE FOR YOUR DATABASES!
AND source_database_id IS NULL
ORDER BY name
SELECT *
FROM #database
DECLARE @id INT, @cnt INT, @SQL NVARCHAR(MAX), @currentDb sysname;
SELECT @id = 1, @cnt = MAX(id)
FROM #database
while @id <= @cnt
BEGIN
SELECT @currentDb = name
FROM #database
WHERE id = @id
SET @SQL = 'select Column1, Column2 from ' + @currentDb + '.dbo.Table1'
print @SQL
EXEC (@SQL);
print '--------------------------------------------------------------------------'
SET @id = @id + 1;
END
GO