Category Archives: Scripts

Easy Security Fixes

Here are some security tasks I’ve been tackling lately.

1.  Find weak passwords and change them.

I keep track of application logins and passwords using a product called KeePass. It has a plugin called strengthreport that you can use to make a report or .csv showing the strength of your passwords. Use the SVN command to checkout that code to a local directory and then copy the StrengthReport folder to wherever you have KeePass installed. Restart KeePass and under the tools menu, you find a few new entries, all labeled ‘Create Report’. Pick one, they all seem to do the same thing. Then generate a report and send to .CSV.

You can filter on the ‘Basic Strength’ column or you can create a column with a single number in it to sort on.

Create a formula based on the ‘Basic Strength’ column: =FIND(“/”,D63)
Then in the next column over do something like this: =MID(D63,E63-1,1)
You could do that all in one block if you’re a fancy sort of excel wizard.

Now that the easy part is done, you can start working with developers to pump these passwords up.

2.  Set policy on logins so that they abide by Active Directory rules on complexity and failed attempts.

You can enable CHECK_POLICY on your logins by running some similar code to what is shown below.  The logins do not unlock themselves after the cooloff time defined in AD.  That’s not great.  Also, instead of clearing the ‘locked’ box on the login, just disable and re-enable CHECK_POLICY for the login (enforce password policy). This will prevent having to change the password, which will in-turn, stop developers from hating you.

 3.  Make sure logins are created properly in the future.

In KeePass, you can define an autotype template for each folder of logins.  This means you can do something like this and variables will be substituted into the template, providing you will a quick way to create users.

CREATE LOGIN [{UserName}] WITH PASSWORD=N'{Password}’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

Just create the login in KeePass, click into a SSMS window, switch back to KeePass and CTRL+V to make that code jump into SSMS.

You can also handle this is with a SQL Server Management Policy using the login facet and the PasswordPolicyEnforced property. That’s the way to go!

Inserting results of FILELISTONLY to a table

I was looking for a code example of this on the web, but couldn’t find it, so here it is. You would need to create the @cmd of course with your own variables.

SELECT  @cmd = 'restore filelistonly  from disk = ''' + @localBackupPath
+ @dbname + '\' + @filename + ''''
PRINT @cmd
CREATE TABLE #files
(
[LogicalName] NVARCHAR(128) ,
[PhysicalName] NVARCHAR(260) ,
[Type] CHAR(1) ,
[FileGroupName] NVARCHAR(128) ,
[Size] NUMERIC(20,0) ,
[MaxSize] numeric(20,0) ,
[FileId] BIGINT ,
[CreateLSN] NUMERIC(25, 0) ,
[DropLSN] NUMERIC(25, 0) ,
[UniqueId] UNIQUEIDENTIFIER ,
[ReadOnlyLSN] NUMERIC(25, 0) ,
[ReadWriteLSN] NUMERIC(25, 0) ,
[BackupSizeInBytes] BIGINT ,
[SourceBlockSize] INT ,
[FileGroupId] INT ,
[LogGroupGUID] UNIQUEIDENTIFIER ,
[DifferentialBaseLSN] NUMERIC(25, 0) ,
[DifferentialBaseGUID] UNIQUEIDENTIFIER ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(32)
)
INSERT #files
EXEC ( @cmd
)

Restore Database From Latest Restore

Here is a bit of code, adapted from Nigel Rivett’s original. You can use this to copy one database over another or restore the most recent database over itself.

--BEGIN TRAN

IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[s_RestoreLatestBackup]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[s_RestoreLatestBackup]
GO

CREATE PROC s_RestoreLatestBackup
@dbname VARCHAR(128) ,
@destdbname VARCHAR(128) ,
@localBackupPath VARCHAR(200) ,
@localDBPath VARCHAR(200) ,
@localLogPath VARCHAR(200) ,
@recipients VARCHAR(128),
@filenamesuffix VARCHAR(50)
AS /*
exec s_RestoreLatestBackup
@dbname = 'hrmprot' ,
@destdbname = 'HRMPROT2',
@localBackupPath = 'K:\Backups\' ,
@localDBPath = 'I:\Data\' ,
@localLogPath = 'J:\Logs\' ,
@recipients = 'myemailaddress',
@filenamesuffix = '_2'
*/

DECLARE @cmd VARCHAR(2000) ,
@filename VARCHAR(128) ,
@s VARCHAR(128) ,
@i INT ,
@d DATETIME ,
@sql NVARCHAR(2000) ,
@StartDate DATETIME

SELECT @StartDate = GETDATE()

-- drop database
IF EXISTS ( SELECT *
FROM master..sysdatabases
WHERE name = @destdbname )
BEGIN
SELECT @cmd = 'drop database ' + @destdbname
EXEC (@cmd)
END

-- get latest backup filename
SELECT @cmd = 'dir /B ' + @localBackupPath + @dbname + '\*.*'
PRINT @CMD
CREATE TABLE #a ( s VARCHAR(2000) )
INSERT #a
EXEC master..xp_cmdshell @cmd

DELETE #a
WHERE s IS NULL
OR s NOT LIKE '%_backup_%'

SELECT *
FROM #a

SELECT @filename = MAX(s)
FROM #a

-- Get files in backup
SELECT @cmd = 'restore filelistonly from disk = ''' + @localBackupPath
+ @dbname + '\' + @filename + ''''
PRINT @cmd
CREATE TABLE #files
(
[LogicalName] NVARCHAR(128) ,
[PhysicalName] NVARCHAR(260) ,
[Type] CHAR(1) ,
[FileGroupName] NVARCHAR(128) ,
[Size] NUMERIC(20,0) ,
[MaxSize] numeric(20,0) ,
[FileId] BIGINT ,
[CreateLSN] NUMERIC(25, 0) ,
[DropLSN] NUMERIC(25, 0) ,
[UniqueId] UNIQUEIDENTIFIER ,
[ReadOnlyLSN] NUMERIC(25, 0) ,
[ReadWriteLSN] NUMERIC(25, 0) ,
[BackupSizeInBytes] BIGINT ,
[SourceBlockSize] INT ,
[FileGroupId] INT ,
[LogGroupGUID] UNIQUEIDENTIFIER ,
[DifferentialBaseLSN] NUMERIC(25, 0) ,
[DifferentialBaseGUID] UNIQUEIDENTIFIER ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(32)
)
INSERT #files
EXEC ( @cmd
)

SELECT *
FROM #files
-- buld the restore command
SELECT @cmd = NULL ,
@s = ''

WHILE @s < ( SELECT MAX(LogicalName)
FROM #files
)
BEGIN
SELECT @s = MIN(LogicalName)
FROM #files
WHERE LogicalName > @s


SELECT @cmd = COALESCE(@cmd + ', move ', '') + '''' + @s
+ ''' to '''
+ CASE WHEN #files.Type = 'D' THEN @localDBPath
ELSE @localLogPath
END
+ CASE WHEN #files.Type = 'D' THEN + LogicalName + @filenamesuffix + '.mdf'
ELSE LogicalName + @filenamesuffix + '.ldf'
END
+ ''''
FROM #files
WHERE LogicalName = @s
END
SELECT @cmd = 'restore database ' + @destdbname + ' from disk = '''
+ @localBackupPath + @dbname + '\' + @filename + ''' with move '
+ @cmd -- + ', standby = ''' + @localBackupPath + 'standby.fil'''

PRINT @cmd

--restore the database
select (@cmd)

exec (@cmd)

drop table #files
drop table #a

-- Check that the restore was successful
select @sql = 'select @i = count(*) from ' + @dbname + '..sysobjects select @d = crdate from master..sysdatabases where name = ''' + @dbname + ''''
exec sp_executesql @sql, N'@i int out, @d datetime out', @i out, @d out

/*
if @d > @StartDate and @i > 20
begin
if @recipients is not null
begin
select @cmd = 'restore ' + @filename + ' completed successfully - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end
end
else
begin
select @cmd = '********************* restore ' + @filename + ' failed - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
if @recipients is not null
begin
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end
raiserror(@cmd, 16, -1)
end
*/
go

--COMMIT TRAN

Creating Restore Statements With Powershell

WARNING: Use caution in running scripts you find on the internet. You are responsible for the well being of your systems!

We’re sitting around waiting for the SAN monkeys to fling some crap our way – not the SAN guys, the SAN monkeys, kinda like gremlins, but not as bad. Our primary cluster is down, which runs 100+ databases. Maybe the disks will come up – or maybe they won’t.

Assessing the situation, I found that the MSDB was not in full recovery mode. People, please don’t run around changing DBs to simple to ‘save space’ without thinking the ramifications through. In this case, we lose all backup history so we now need to figure out what backup file is the most recent and then which log files come after that .bak file – then we slam them together into a file, leaving the NORECOVERY clause off the last log restore statement. If you think I’m doing this by hand, you’re crazy.

We make an assumption here. Each file holds only one backup set. This is how we do our backup files, so I’m OK with this assumption.


$DatabaseBackupRoot = “\\MYSERVA\e$\Backups\"
$fix = ""

$DatabaseBackupDirs = gci $DatabaseBackupRoot

foreach ($DatabaseBackupDir in $DatabaseBackupDirs)
{
$DB = $DatabaseBackupDir.Name

$BackupRestore = ""
$LogRestore = ""
$LogRestore = ""
$BackupRestore = ""
# Parameters

$Path = “\\MYSERVA\e$\Backups\$DB”;
$FullPattern = “*.bak”;
$LogPattern = “*.trn”;

# Enumerate Last Full Back
$FullBackup = Get-ChildItem $Path -Filter ($DB + $FullPattern) `
| sort Name `
| Select-Object -Last 1;

# Enumerate Log Backups
$LogBackups = Get-ChildItem $Path -Filter ($DB + $LogPattern) `
| Where-Object { $_.LastWriteTime -gt $Fullbackup.LastWriteTime} `
| sort Name;

$LogBackupsCount = $LogBackups.Count

$BackupFileName = $FullBackup.Name
# if the backupfile is null, skip this
if ($BackupFileName)
{

if ($LogBackupsCount > 0)
{
$BackupRestore = "RESTORE DATABASE [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$BackupFileName' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
"
}
else
{
$BackupRestore = "RESTORE DATABASE [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$BackupFileName' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
"
}

}
else{
#We get a list in the output of emtpy directories.
Write-Output $DB
}

$counter = 0
$len = $LogBackups.Count

foreach ($LogBackup in $LogBackups)
{
$counter++

$LogBackupFile = $LogBackup.Name;
#Test for no log/empty directory
if($LogBackupFile)
{
if($counter -lt $len)
{
$LogRestore = $LogRestore + "
RESTORE LOG [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$LogBackupFile' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
"
}
else
{
$LogRestore = $LogRestore + "
RESTORE LOG [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$LogBackupFile' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
"

}
}

}

$RestoreOneDatabase = $BackupRestore + $LogRestore

$RestoreAllDatabases = $RestoreAllDatabases + $RestoreOneDatabase

}

$RestoreAllDatabases | out-file "c:\temp\restore-MYSERVA.sql"

So now we’ve got a SQL statement with all these restores compiled together and separated with GO statements. Pretty neat. I’d probably run small blocks of them and fix any issues the script might have.

See all roles on your server

This cursor inserts all of the roles and members from each database into your admin database.


USE [SQL_DBA_Sandbox]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Roles](
[DBName] [varchar](max) NULL,
[Role] [varchar](max) NULL,
[Member] [varchar](max) NULL
) ON [PRIMARY]
END
ELSE
DELETE FROM SQL_DBA_Sandbox.dbo.Roles;
GO

DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;

DECLARE @SQL VARCHAR(2000);

DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.databases
WHERE database_id > 4

OPEN Cursor_DB

FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL =
'INSERT INTO SQL_DBA_Sandbox.dbo.Roles
( DBNAME, ROLE, MEMBER )
SELECT ''' + @DB_NAME + ''',
r.name AS ''role'',
mem.name AS ''member''
FROM [' + @DB_Name + '].sys.database_role_members rm
INNER JOIN [' + @DB_Name + '].sys.database_principals r ON r.principal_id = rm.role_principal_id
INNER JOIN [' + @DB_Name + '].sys.database_principals mem ON mem.principal_id = rm.member_principal_id';
PRINT @SQL;

EXEC (@SQL);

FETCH NEXT FROM Cursor_DB INTO @DB_NAME

END

CLOSE Cursor_DB
DEALLOCATE Cursor_DB

SELECT DISTINCT DBName,[Role] FROM SQL_DBA_Sandbox.dbo.Roles
WHERE Role = 'analyst'

SELECT * FROM SQL_DBA_Sandbox.dbo.roles
WHERE Member = 'my_user'

See all the schemas on your server

Oh no! A useful cursor?


DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;

CREATE TABLE #RESULTS
(TABLE_CATALOG sysname,
TABLE_SCHEMA sysname)

DECLARE @SQL VARCHAR(2000);

DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.databases
WHERE database_id > 4

OPEN Cursor_DB

FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'USE ['+ @DB_NAME + '];
INSERT INTO #RESULTS
( TABLE_CATALOG, TABLE_SCHEMA )
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
UNION ALL
SELECT DISTINCT ROUTINE_CATALOG,ROUTINE_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES;'

PRINT @SQL;

EXEC (@SQL);

FETCH NEXT FROM Cursor_DB INTO @DB_NAME

END

CLOSE Cursor_DB
DEALLOCATE Cursor_DB

SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA FROM #RESULTS
WHERE TABLE_SCHEMA <> 'DBO'