Art of the DBA Rotating Header Image

February 10th, 2015:

#tsql2sday 63 – Security, Default Databases, and #Powershell

This month’s T-SQL Tuesday is about how we manage database security, hosted by the ever effervescent Kenneth Fischer(@sqlstudent144). While I didn’t have a lot of time to work on this post, I wanted to share with you all a quick little nugget I used for one of my most recent presentations.

So, default databases. This is a security bit that usually gets skipped over, unfortunately, but I think it’s a pretty important practice that is easy to implement and maintain. Let’s be honest, most of us leave the default database as ‘master’ when we create them. This then will usually lead us to accidentally doing work in the master, which potentially could be disastrous.

2042603602_fc289cf395_zSecurity is more than just locking people out or letting people in. While most of the situations with master can be be limited by reduced permissions, it doesn’t do anything to help or manage accounts that need to be able to work in that database.  Security is not just about active policies, but also passive rules to reduce and prevent mistakes. A lot of damage can be prevented by directing people out of dangerous places by default.

Fixing this is easy:

ALTER LOGIN [foo\bar] WITH DEFAULT_DATABASE = [tempdb];

Also, we’d ideally create the logins with that default database set to begin with:

CREATE LOGIN [foo\bar] FROM WINDOWS WITH DEFAULT_DATABASE=[tempdb];

However, this can be difficult to manage and keep up to date, especially since SQL Server doesn’t help by always defaulting to master. One thing that can be done is to have a way to set all your logins’ default databases to an appropriate database (I really like tempdb for this). So how about some Powershell?

Using the SMO, we can use the DefaultDatabase property of the Login class for a quick and easy way to alter our default database:

$login.DefaultDatabase = ‘tempdb’

Then, using the Logins collection in our Server object, we have an easy loop to go through an alter these default databases. Wrap that up in a fucntion and Bob’s your uncle. Well, he’s my uncle, not yours. Anyway, we’ll get a little something like this:

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$ErrorActionPreference = 'Inquire'

function Set-SQLDefaultDatabases{
    param([string[]]$Instances = 'localhost'
         ,[string]$defaultdb = 'tempdb')

foreach($InstanceName in $Instances){
    $smosrv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $InstanceName
    if($smosrv.Databases.name -contains $defaultdb){
        foreach($login in $smosrv.Logins){
            Write-Verbose "Altering $login on $InstanceName to $defaultdb"
            $login.DefaultDatabase = $defaultdb
            }
        }
    else{
        Write-Warning "Database $defaultdb is not valid on $InstanceName."
        }
    }
}

Set-SQLDefaultDatabases

So this simple function will set all the logins (SQL, Windows, and AD Groups) to the default database of choice. It’s fairly brute force, but gives you a good starting point. At this point, you should be able to see the value of writing it in this function format and use techniques I’ve blogged about earlier to set this enterprise wide.

Thanks out to Ken for hosting this month’s T-SQL Tuesday! Looking forward to reading the other blogs!