Art of the DBA Rotating Header Image

#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!

3 Comments

  1. Andy Warren says:

    Hey Mike, hope you’re well!

    How is it better to have them create objects in TempDB? I’ve used master as a default and required devs to put the catalog in the connection string, makes things very clear, and it avoids an issue I saw years ago when the database id changed due to some restores and the default dbid then pointed to the wrong db! To me the point is to not let anyone create anything in master. If you do change to Tempdb, do you then check to make sure you’re not carrying permanent objects?

    If it’s style, I’m ok with that, but is there a real difference in security? I’m not clear on that?

    Looking forward to your thoughts,

    Andy

  2. […] default database as a good best practice (and I tend to agree). Is anyone surprised that he decided to give us a powershell script to change […]

  3. […] default database as a good best practice (and I tend to agree). Is anyone surprised that he decided to give us a powershell script to change […]

Leave a Reply

Your email address will not be published. Required fields are marked *