qnd:sql_server [Whizzbang Tech Stuff]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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>