Art of the DBA Rotating Header Image

database security

Copying SQL Logins with #Powershell

Managing SQL server logins, especially in the days of availability groups, can be quite the pain in the butt. Usually we’ll make use of scripts like sp_help_revlogin to manage this, copying logins en masse from one instance to another.  This works, but it’s pretty brute force and there might be logins you may not want to copy across.

So let’s use Powershell to be a little more…elegant. There’s a couple things we can use to get a nice login copying function that is flexible and robust.  The first is the .Script() method in the SMO’s Login object. This is actually a piece of functionality many of us use daily, whenever we script out objects in SQL Server Management Studio. The trick is to access this programmatically, which is a snap in Powershell.  The basic syntax is something like this:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$smoserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server localhost

What’s awesome about this method is the SMO will also script out all of the additional server level options, like default database and server roles.  Very handy for maintaining the consistency of the logins from one instance to another.

There are a couple of gotchas. First , if we want to include the SID in our script, we have to create a  ScriptingOptions object and specify that the LoginSid should be included. Second, if the login is a SQL Server login, the .Script() method will add in a line to disable the login and give it a completely random password. This is done for security reasons, so that any login scripted out can’t create a possible security hole. As an FYI, you’ll get the same if you script out the login in SSMS.

So what do we do? Managing passwords across instances is tricky, but must be done, otherwise things will start to break. With Windows based logins it’s simple, since that’s all managed in the Active Directory. SQL logins require more care. To handle this in T-SQL, you’ll want to create the login with the SQL Server password hash. Unfortunately, the SMO doesn’t provide an easy way to do this, so we have to get clever. To be clever, we have to:

  1. Retrieve the binary hash value from the source SQL instance.
  2. Convert the binary hash to a string for use in a CREATE LOGIN statement.
  3. Insert the password hash string into our CREATE LOGIN statement in the correct place.

Easy enough, right?  Honestly, all it takes is some string manipulation, which Powershell is pretty darn good at. Certainly better than SQL Server itself.  Turns out this is the biggest hurdle to the process and, once solved, the rest of the script comes together quite nicely.

The following function brings all these concepts together. It’s a little more complex than other scripts I’ve posted, but here’s the highlights:

  • You must declare a source instance. This is the instance from which all logins will be scripted from.
  • You can specify a string array/collection of logins. The function will only script out the logins you list. If you don’t list any (argument is null), the function will script out all non-system logins (everything but sa, pretty much).
  • Because I like the pattern of scripts to build scripts, the function will create a .sql script of all your create login statements. By default this will go to your My Documents folder, but you can specify another directory if you want.
  • If you provide an instance to the ApplyTo parameter, the function will attempt to create the logins on that instance if they don’t exist. Pass the -Verbose switch if you want to see which ones it creates and which ones it skips.
function Copy-SQLLogins{
    param([parameter(Mandatory=$true)][string] $source
            ,[string] $ApplyTo
            ,[string[]] $logins
            ,[string] $outputpath=([Environment]::GetFolderPath("MyDocuments")))
#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#create initial SMO object
$smosource = new-object ('Microsoft.SqlServer.Management.Smo.Server') $source

#Make sure we script out the SID
$so = new-object
$so.LoginSid = $true

#set output filename
$filename = $source.Replace('/','_') + '_' + (Get-Date -Format 'yyyyMMddHHmm') + '_logins.sql'
$outfile = Join-Path -Path $outputpath -ChildPath $filename

#If no logins explicitly declared, assume all non-system logins
    $logins = ($smosource.Logins | Where-Object {$_.IsSystemObject -eq $false}).Name.Trim()

foreach($loginname in $logins){
    #get login object
    $login = $smosource.Logins[$loginname]

    #Script out the login, remove the "DISABLE" statement included by the .Script() method
    $lscript = $login.Script($so) | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}
    $lscript = $lscript -join ' '

    #If SQL Login, sort password, insert into script
    if($login.LoginType -eq 'SqlLogin'){

      $sql = "SELECT convert(varbinary(256),password_hash) as hashedpass FROM sys.sql_logins where name='"+$loginname+"'"
      $hashedpass = ($smosource.databases['tempdb'].ExecuteWithResults($sql)).Tables.hashedpass
      $passtring = ConvertTo-SQLHashString $hashedpass
      $rndpw = $lscript.Substring($lscript.IndexOf('PASSWORD'),$lscript.IndexOf(', SID')-$lscript.IndexOf('PASSWORD'))

      $comment = $lscript.Substring($lscript.IndexOf('/*'),$lscript.IndexOf('*/')-$lscript.IndexOf('/*')+2)
      $lscript = $lscript.Replace($comment,'')
      $lscript = $lscript.Replace($rndpw,"PASSWORD = $passtring HASHED")

    #script login to out file
    $lscript | Out-File -Append -FilePath $outfile

    #if ApplyTo is specified, execute the login creation on the ApplyTo instance
        $smotarget = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ApplyTo

        if(!($ -contains $loginname)){
            $outmsg='Login ' + $ + ' created.'
            $outmsg='Login ' + $ + ' skipped, already exists on target.'
        Write-Verbose $outmsg

There is one additional piece to this, and that’s a helper function for converting the binary hash value to a string. It’s pretty simple, taking the value passed to it and doing some formatting magic to get a value for the SQL statement.

function ConvertTo-SQLHashString{
  param([parameter(Mandatory=$true)] $binhash)

  $outstring = '0x'
  $binhash | ForEach-Object {$outstring += ('{0:X}' -f $_).PadLeft(2, '0')}

  return $outstring

Bring this all together and now we have a straightforward function we can call to help us manage our logins. Some examples:

#Create a SQL Script in My Documents for all logins
Copy-SQLLogins -source 'localhost'

#Create a SQL Script in My documents for the passed logins
Copy-SQLLogins -source 'localhost' -logins @('SHION\mike','test','pcuser')

#Create a SQL Script in My documents for the passed logins and try and create these logins on the target SHION\ALBEDO
Copy-SQLLogins -source 'localhost' -logins @('SHION\mike','test','pcuser') -ApplyTo 'SHION\ALBEDO' -Verbose

Hopefully this helps you in your day to management of your instances. I’ve used this script for migrations and managing my Availability Group nodes. In my opinion, it’s a heck of a lot cleaner than sp_help_revlogin (a venerable tool in its own right, to be sure). As always, this along with my other scripts, can be found up on my GitHub repository

Finding Orphan Logins

Cleaning up old security info is always something that’s hard to stay on top of.  Employees who leave or change responsibilities can make it difficult to track which logins need to remain in our instances.  The best way to manage this is by using Active Directory groups and managing access from the group level instead of by explicit login.  However, because of the communication and organization that’s required to set up these groups in the first place, it’s not uncommon to find ourselves managing explicit logins in our severs.

The unfortunate side effect is this typically results in orphaned logins within our SQL Servers.  A question on how to manage this came up recently on and, while I couldn’t find a way to answer the specific question (because it was focused around finding orphaned logins in SQL 2000), I was able to come up with a handy utility script for 2005+ that makes use of xp_logininfo.  It’s not a complex script and provides a list of the actual drop login statements you can execute as part of your cleanup.

declare @user sysname
declare @domain varchar(100)

--store total output
declare @return table
(login_name sysname
,sql_statement varchar(max)
,error_msg varchar(100))

--Store output of xp_logininfo
declare @junk table
(a_name sysname NULL
,a_type char(8) NULL
,priv char(9) NULL
,m_login sysname NULL
,p_path sysname NULL)

--Declare domain you are checking
set @domain = 'FOO'

declare recscan cursor for
select name from sys.server_principals
where type = 'U' and name like @domain+'%'

open recscan
fetch next from recscan into @user

while @@fetch_status = 0
	begin try
		insert into @junk
		exec xp_logininfo @user
	end try
	begin catch
		--Error on sproc because login does not exist
		insert into @return
		values(@user,'drop login '+convert(varchar,@user),ERROR_MESSAGE())
	end catch

	fetch next from recscan into @user

close recscan
deallocate recscan

select * from @return

A couple notes on this script:

  • It currently doesn’t handle removing the logins from individual databases.  I have another script I use to do that and will be working on combining the two.
  • I don’t automatically execute the drop login because I’m paranoid.  :)  By providing a list, I can review the logins, along with the error message output, and verify that I want to remove them

Hopefully this little utility script can help you with login management in your environments.

Monday Scripts – xp_logininfo

How many times have you wished you could use SQL Server to query Active Directory and see who is in that group? This week’s script will allow you to do that, so if you have AD groups set up for logging into your server you can see exactly which logins have access.

xp_logininfo is an extended stored procedure that does just that. By passing a couple parameters, you can easily query any AD group that is a server principal. Wrap it up in a dynamic SQL loop, and you can quickly and easily audit your server’s security.

declare @winlogins table
(acct_name sysname,
acct_type varchar(10),
act_priv varchar(10),
login_name sysname,
perm_path sysname)

declare @group sysname

declare recscan cursor for
select name from sys.server_principals
where type = 'G' and name not like 'NT%'

open recscan
fetch next from recscan into @group

while @@FETCH_STATUS = 0
insert into @winlogins
exec xp_logininfo @group,'members'
fetch next from recscan into @group
close recscan
deallocate recscan

from (select * from sys.server_principals where type = 'R') r
join sys.server_role_members rm on (r.principal_id = rm.role_principal_id)
join (select * from sys.server_principals where type != 'R') u on rm.member_principal_id = u.principal_id
left join @winlogins wl on = wl.perm_path
order by login_name,r.principal_id,u.type_desc,

Looking at the script, I first do a looped scan through sys.server_principals for all Windows groups (type ‘G’) and collect their AD group info. I store that in a table variable, then join it back to a select from server_principals again for a full listing of all my server permissions. Bam! Instant security report.

Where’d that table go?

The other day I was working with Doug Lane (b|t) over Twitter on an object visibility issue. He was trying to hide objects from linked ODBC selection to a SQL Server, particularly the system views in his database. I was able to help him out, but it got me wondering about exactly what allows you to see objects. I mean, I always knew I could see something if I had SELECT on it, but was that what really controlled the visibility?

I see you!

Seeing a table boils down to being able to view the metadata in the database, which means we must be given the VIEW DEFINITION privilege. This permission was new to 2005, as previous versions of SQL Server would allow any user in the public role to view object metadata. You can GRANT, REVOKE, or DENY this privilege on any of the four levels within SQL Server: server, database, schema, and object. Many actions implicitly grant VIEW DEFINITON, such as granting a user SELECT privileges to a table or EXECUTE on a stored procedure.

Let’s look at a couple examples. I’ll go ahead and create demo_login user on my test instance and add him to my database, but we won’t give him any permissions yet:

use msf_test
create login demo_login with password='dummy';
create user demo_login for login demo_login;

Now, in my test database, I have a table called demo1. My login, demo_login, hasn’t been granted any privileges to this table,so I can’t see the table in my object browser.

As soon as I grant a permission on it, like SELECT, the table appears:

grant select on demo1 to demo_login;

Again, this is because any GRANT on our table implicitly grants VIEW DEFINITION.

Now just like any any other privilege, we can explicitly DENY that privilege to a user and override (almost) any implicit grant. So, if we want to prevent a user from viewing the metadata on an object, but still allow him to select from it, we simply have to deny him VIEW DEFINITON:

deny view definition on demo1 to demo_login;

And even though I can’t see the object, I can still query it.

You can’t stop me! I’m invincible!

The caveat to explicitly denying permissions to a user is that three access levels can not be denied view permissions. These levels are:

  • Server sysadmin role
  • The user owns the object in question
  • The database db_owner role (implied ownership of all database objects)

If any of these levels apply, then the user will always be able to see the object. To boot, the system views are a little tricky. I was unable to explicitly deny VIEW DEFINITION to either the INFORMATION_SCHEMA or the sys schemas and prevent them from being visible. This one will take a little more research, but in the interim I know that you can prevent them from being seen in a linked ODBC manager by explicitly denying select on individual system views.

To wrap up, the VIEW DEFINITION is the pivot point of visibility. If you have it, even by accident, you can see that object in any of the methods that list database objects (SSMS object browser, ODBC link manager, etc). There are some tricks to it, but it can be a useful tool if you have specific visibility and permission requirements.

Monday Scripts – Adding database roles in bulk

As DBAs we’re in charge of granting access for our databases. Typically we can handle this using active directory groups and SQL Server Management Studio, but this can turn in to a pretty cumbersome and tedious task as the number of databases we have to manage grows. Currently, it’s not unusual for a user to ask me for read permissions on a server that has 20+ databases.   Since it doesn’t really make sense for me to add that user manually to each database on the server (both for time and the likelihood that it will create an error), I’ve created this script for adding a user to the same roles in every user database across a server.

/*Get Database Cursor*/
declare recscan cursor for
select name
from sys.databases
where name not in ('master','tempdb','model','msdb')

/*declare variables*/
declare @db varchar(100)
declare @sql varchar(4000)
declare @user varchar(100)
declare @roles varchar(max)
declare @role varchar(50)
declare @winuser bit
declare @message varchar(max)

/*string parsing variables*/
declare @strpos int
declare @strlen int
set @strpos = 0

/*Set user name and roles to be assigned, comma separated string.
@winuser defines if user is windows(1) or not(0).*/

set @winuser = 0
set @user = <INSERT USER NAME>/*User name, such as DOMAIN\msfal for windows or report_reader for direct db login*/
set @roles =<Comma list of roles>/*i.e.'db_datareader,db_datawriter'*/

/*create server login*/
Print 'Creating user ['+@user+'] on server...'
if @winuser = 1
	set @sql= 'USE [master] CREATE LOGIN ['+@user+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'
	set @sql= 'USE [master] CREATE LOGIN ['+@user+'] WITH PASSWORD = '''+@user+'!123'''

/*Cycle through DBs to assign roles.*/
open recscan
fetch next from recscan into @db
while @@fetch_status = 0
	/*Add user if user does not exist in database.*/
	print 'Adding ['+@user+'] to ['+@db+']...'
	set @sql = 'USE ['+@db+'] CREATE USER ['+@user+'] FOR LOGIN ['+@user+']'

	/*Parse role string and assign each role to the user.*/
	Print @db + char(10) + '****************************'
	while @strpos < LEN(@roles)
		select @strlen = case when charindex(',',@roles,@strpos) = 0 then LEN(@roles)-@strpos+1 else charindex(',',@roles,@strpos) -@strpos end
		select @role = SUBSTRING(@roles,@strpos,@strlen)

		print char(9)+'Adding '+@user+' to '+@role+' role in '+@db+'...'
		set @sql = 'USE ['+@db+'] EXEC sp_addrolemember N'''+@role+''', N'''+@user+''''
		select @strpos = case when charindex(',',@roles,@strpos) = 0 then LEN(@roles) else charindex(',',@roles,@strpos) + 1 end

print char(10)
fetch next from recscan into @db
set @strpos = 0

/*clean up cursor*/
close recscan
deallocate recscan

The script itself may look intimidating, but really it breaks down into 3 steps:

1 – Create the user as a server login
Then, for each database
2 – Create the user within the database
3 – Assign the user to the each role within the passed list of roles.

To use this script, all you need to do is set @user to the name of your user, set @winlogin to 1 or 0 if your user is an Active Directory login or not, then insert a comma separated list of roles you want to add the user to. Note, you’re not restricted to the default roles, you can use custom created roles in the script. Just make sure the role exists.

The only other item of interest here is the code section that parses through the role array. Take a look at it if you’re looking for away to split a string into its component parts using T-SQL.

Feel free to download the script here.