Differences

This shows you the differences between two versions of the page.

--- qnd:sql_server [2021/12/28 14:44] – created mgupton
+++ qnd:sql_server [2021/12/30 19:25] (current) – mgupton
@@ Line 1: / Line 1: @@
 ====== SQL Server ======
   * [[qnd:SQL Server Security]]
+====== Solutions ======
+  * [[https://github.com/mgupton/mssql-check]], Powershell solution that runs SQL scripts against all instances
+====== Example Queries ======
+=== Get Config Settings ===
+<code sql>
+SELECT *
+FROM sys.configurations
+</code>
+=== Select Into Variable ===
+<code sql>
+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
+</code>
+===  ===
+<code sql>
+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
+</code>
+=== Execute Query Against Every Database ===
+  * [[https://stackoverflow.com/questions/4726899/while-loop-to-iterate-through-databases]]
+<code sql>
+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
+</code>