Open source · dbatools

Copy and audit SQL Server logins with dbatools

If you retire an instance, move databases to a new server or build a test environment, you need the same logins there: with the same passwords, the same SIDs and the same permissions. If any of that is missing, applications fail to log in, or users in the databases are left without a login.

Tested with dbatools 2.9.1 on Nov 18, 2026 · approx. 16 min read

Part 5 · LoginsSQL queries 3Lab Windows · Linux
Copy-DbaLogin · app_adminlab · Windows and LinuxsourcedestinationSIDSID✓password hashpassword hash✓dbcreatordbcreator✓srv_monitormissingsrv_monitorsrv_monitorIMPERSONATEmissingIMPERSONATEIMPERSONATEStatus: Successfulno message

dbatools has a set of commands for this: Copy-DbaLogin transfers logins, Export-DbaLogin and Get-DbaUserPermission document permissions. In our lab, SID and password came across reliably. Some permissions were missing afterward, though, without the command saying so.

This page shows the approach that worked in the lab, what you should compare afterward, and where the result and the messages diverge. Tested with dbatools 2.9.1 on Nov 18, 2026, mainly on Windows with two instances on one server, plus runs against SQL Server 2017 to 2025 (method). Orphaned users are covered in part 6.

In 60 seconds

  • SID and password come across. Copy-DbaLogin transfers SQL logins with SID, password hash, policy settings and disabled state. After a restore, the database users match without repair.
  • Not every permission comes across, and there is no message. Memberships in user-defined server roles missing on the destination, and permissions on other logins such as IMPERSONATE, are lost, for Windows logins and groups as well. Compare source and destination.
  • Database first, then the login. Otherwise dbatools sets the default database to master, and it stays that way even after the restore.
  • Two instances on one server: Without -ExcludeSystemLogins, the command creates the source instance's service accounts on the destination, two of them with sysadmin.
  • -NewSid is for cloning. In a migration, it leaves the users orphaned.
  • Documenting permissions has side effects. Export-DbaLogin writes password hashes into the file, Get-DbaUserPermission in practice needs sysadmin.
  • SQL Server 2025 rewrites copied password hashes into a new format at the first login. After that, they can no longer be loaded back into SQL Server 2019.

1 · What belongs to a login

A login lives on the instance, a user in the database. The two are linked by the SID, not by the name. That is why a user may have a different name than its login, and why a restored database finds its logins only if their SIDs are the same on the new instance. If no SID matches, the user is orphaned: it still has its permissions in the database, but nobody can log in as that user. How to find and map such users is shown in part 6.

For SQL logins, a login also includes the password hash, the password policy and expiration settings, the disabled state, the default database, memberships in server roles and server-level permissions. Section 2 shows which of these Copy-DbaLogin transfers.

That all of this can be moved with one command is thanks to the volunteers of the dbatools project. After 2.9.1, -UpdateExisting was added for logins that already exist (#10801); we did not test it.

The commands and parameters on this page (2.9.1 help and source code):

Command, parameterEffect
Copy-DbaLogin -Source … -Destination …transfers logins with SID, hash, roles, permissions and database users
-Login, -ExcludeLoginselection; without it, all logins except sa and ##…##
-ExcludeSystemLoginsleaves out service and system accounts (according to the source code, section 3)
-Force, -KillActiveConnectiondrop and recreate an existing login; end open sessions
-NewSid, -LoginRenameHashtableassign a new SID, rename the login (for cloning, section 3)
Export-DbaLoginwrites logins, roles and permissions as a T-SQL script; -ExcludePassword without hashes
Get-DbaUserPermissionlists roles and permissions at server and database level
Test-DbaLoginPasswordchecks SQL logins for empty, trivial or known passwords

2 · Copying logins and comparing the result

In the lab, a Windows server ran two instances: SQLSRV-01 as the source and SQLSRV-01\SQL2 as the destination, both SQL Server 2022, connected with Windows authentication. The source had a database kasse and these logins:

  • app_leser: default database kasse, member of db_datareader there.
  • app_admin: member of dbcreator and of the user-defined server role srv_monitor, plus VIEW ANY DEFINITION and IMPERSONATE ON LOGIN::app_leser.
  • alias_login: its user in kasse is called kassierer.
  • gesperrt: disabled.
  • pruefer: VIEW ANY DEFINITION, VIEW SERVER STATE, CONNECT ANY DATABASE.
  • SQLSRV-01\dbt_winuser (local Windows user, default database kasse) and SQLSRV-01\dbt_wingruppe (local Windows group, member of srv_monitor).

This is what the source looked like, read with the query further down (excerpt):

Lab output
name                              type_desc     is_disabled default_database_name rollen                rechte
----                              ---------     ----------- --------------------- ------                ------
alias_login                       SQL_LOGIN           False kasse
app_admin                         SQL_LOGIN           False master                dbcreator,srv_monitor GRANT VIEW ANY DEFINITION,GRANT IMPERSONATE ON LOGIN::app_leser
…
NT Service\MSSQLSERVER            WINDOWS_LOGIN       False master                sysadmin
NT SERVICE\SQLSERVERAGENT         WINDOWS_LOGIN       False master                sysadmin
…
SQLSRV-01\dbt_wingruppe           WINDOWS_GROUP       False master                srv_monitor
SQLSRV-01\dbt_winuser             WINDOWS_LOGIN       False kasse

The database kasse did not exist on the destination yet. We copied all logins in one go:

# Lab shortcut for self-signed certificates, this session only, connection stays encrypted (part 1)
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true

Copy-DbaLogin -Source SQLSRV-01 -Destination SQLSRV-01\SQL2 |
    Format-Table Name, Type, Status, Notes -AutoSize -Wrap
Lab output
Name                      Type                 Status     Notes
----                      ----                 ------     -----
alias_login               Login - SqlLogin     Successful Database kasse does not exist on [SQLSRV-01\SQL2], switching DefaultDatabase to 'master' for alias_login
app_admin                 Login - SqlLogin     Successful
app_leser                 Login - SqlLogin     Successful Database kasse does not exist on [SQLSRV-01\SQL2], switching DefaultDatabase to 'master' for app_leser
gesperrt                  Login - SqlLogin     Successful Database kasse does not exist on [SQLSRV-01\SQL2], switching DefaultDatabase to 'master' for gesperrt
NT AUTHORITY\SYSTEM       Login - WindowsUser  Skipped    Already exists on destination
NT Service\MSSQLSERVER    Login - WindowsUser  Successful
NT SERVICE\SQLSERVERAGENT Login - WindowsUser  Successful
NT SERVICE\SQLTELEMETRY   Login - WindowsUser  Successful
…
pruefer                   Login - SqlLogin     Successful
SQLSRV-01\Administrator   Login - WindowsUser  Skipped    Already exists on destination
SQLSRV-01\dbt_wingruppe   Login - WindowsGroup Successful
SQLSRV-01\dbt_winuser     Login - WindowsUser  Successful Database kasse does not exist on [SQLSRV-01\SQL2], switching DefaultDatabase to 'master' for SQLSRV-01\dbt_winuser

For the four logins with default database kasse, the same message also appeared as a warning. Everything else was "Successful". The comparison afterward:

PropertySourceDestination after Copy-DbaLogin
SID of all copied logins–identical
Password hash of the SQL logins–identical; login with the old password succeeded
gesperrtdisableddisabled: Login failed for user 'gesperrt'. Reason: The account is disabled.
Default database of app_leser, alias_login, gesperrt, dbt_winuserkassemaster
Server roles of app_admindbcreator, srv_monitoronly dbcreator
Server role of the group dbt_wingruppesrv_monitornone
Server permissions of app_adminVIEW ANY DEFINITION, IMPERSONATE ON LOGIN::app_leseronly VIEW ANY DEFINITION
Server permissions of prueferthree permissionsall three

Between two instances of versions 2019 and 2025, the same pattern played out; the policy settings CHECK_POLICY and CHECK_EXPIRATION came across as well.

What is missing and why

Two things are missing without any message, for SQL logins just as for Windows logins and groups:

  • Memberships in user-defined server roles that do not exist on the destination. dbatools only transfers memberships in roles that already exist on the destination. Create user-defined server roles first, for example with Copy-DbaServerRole (not tested by us).
  • Permissions on other logins such as IMPERSONATE ON LOGIN::…. dbatools only reads permissions on the server itself, not on logins. This is known in the project and open (#10834, since Nov 11, 2026). Until it is fixed, add such permissions by hand.

You see both gaps when you read source and destination with the same query and compare:

-- Logins with default database, server roles and server permissions (read-only)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT sp.name, sp.type_desc, sp.is_disabled, sp.default_database_name,  (SELECT STRING_AGG(r.name, ',')     FROM sys.server_role_members AS m     JOIN sys.server_principals AS r ON r.principal_id = m.role_principal_id    WHERE m.member_principal_id = sp.principal_id) AS rollen,  (SELECT STRING_AGG(p.state_desc + ' ' + p.permission_name            + CASE p.class WHEN 101 THEN ' ON LOGIN::' + SUSER_NAME(p.major_id) ELSE '' END, ',')     FROM sys.server_permissions AS p    WHERE p.grantee_principal_id = sp.principal_id      AND p.permission_name <> 'CONNECT SQL') AS rechteFROM sys.server_principals AS spWHERE sp.type IN ('S', 'U', 'G')   -- SQL logins, Windows users, Windows groupsORDER BY sp.nameOPTION (MAXDOP 1);

STRING_AGG is available from SQL Server 2017. On the destination, the query returned only dbcreator and GRANT VIEW ANY DEFINITION for app_admin, and no role for the group. (The column names rollen and rechte are German for roles and permissions; we kept them as in the lab.)

3 · Three pitfalls when copying

Default database and -Force

If a login's default database does not exist on the destination yet, Copy-DbaLogin sets master and says so (source code 2.9.1, lines 494–497). It stays that way after the restore: app_leser still landed in master in the lab. Applications that do not specify a database then work in the wrong context.

Recreating the login with -Force failed in the lab (copy from SQL Server 2019 to 2025):

Lab output
Could not drop login 'app_leser' as the user is currently logged in.

The session came from the connection pool of one of our own login tests. The command ends open sessions only with -KillActiveConnection (source code, lines 455–465, not measured), which is an intervention on a server with applications attached. The simpler way is the right order: restore the databases first, then copy the logins. Then Copy-DbaLogin keeps the default database (as with the clone further down), and the users find their logins by SID. We measured the reverse order: there, too, all users were mapped after the restore, including kassierer and the Windows users; only the default database stayed wrong. If a database is restored before the logins exist, however, its users are orphaned at first (part 6).

Two instances on the same server

Between SQLSRV-01 and SQLSRV-01\SQL2, Copy-DbaLogin created the source instance's service accounts on the destination: NT Service\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT with sysadmin, NT SERVICE\SQLTELEMETRY with its server permissions, all "Successful" (output in section 2). The destination has its own service accounts (NT Service\MSSQL$SQL2, NT SERVICE\SQLAgent$SQL2); the source's accounts need no permissions there.

According to the source code (lines 304–325), the command skips logins with the prefix NT or the computer name only if source and destination are on different computers. For consolidation or a move between instances of one server, set -ExcludeSystemLogins or exclude the accounts with -ExcludeLogin. Both follow from the source code; we measured the call without these parameters.

Between two computers, the command skipped such accounts with "Local machine name", seen on Linux, not between two Windows servers. If the source or destination is SQL Server on Linux, it also skips BUILTIN\Administrators ("BUILTIN\Administrators is a critical system login"). Windows logins work on Linux only with an Active Directory integration (SQL Server on Linux for Windows admins).

-NewSid is for cloning

-NewSid gives the login a new SID on the destination. For a clone, that fits:

Copy-DbaLogin -Source sql2019 -SourceSqlCredential $cred -Destination sql2025 -DestinationSqlCredential $cred `
    -Login app_leser -LoginRenameHashtable @{ app_leser = 'app_leser_klon' } -NewSid

In the lab, this created app_leser_klon with a new SID, default database kasse and a user with db_datareader in kasse. Login with the old password succeeded.

In a migration, -NewSid does harm. We restored kasse on SQL Server 2022 without logins and then copied four logins with -NewSid, all "Successful". The users in kasse kept their old SIDs and stayed orphaned. Login failed because the default database could not be opened for the new login:

Lab output
Cannot open user default database. Login failed.
Login failed for user 'app_leser'.

The error log added Failed to open the database 'kasse' specified in the login properties. So for a migration, copy without -NewSid. How to map users orphaned this way is shown in part 6.

4 · SQL Server 2025 rewrites copied hashes

According to Microsoft, SQL Server 2025 uses PBKDF2 for password hashes by default (What's new in SQL Server 2025, Security section). In the lab, this had a consequence that the page does not mention: a copied hash in the old format lasts only until the first login.

We copied a login from SQL Server 2019 to 2022 and 2025 and logged in once on each instance:

InstanceHash after Copy-DbaLoginafter the first loginnewly created login
2019 CU32 (source)0x0200… (original)unchanged0x0200…
2022 CU270x0200…, same as sourceunchanged0x0200…
2025 CU90x0200…, same as source0x0300…, different hash0x0300…

The password stays the same, and login keeps working. You should know three consequences:

  • Hash comparisons report differences. If you compare source and destination by their hashes after a migration, you see different values on 2025 after the first login, although nothing has changed.
  • The way back by hash is limited. We loaded the new hash from 2025 back with CREATE LOGIN … PASSWORD = 0x… HASHED, the same way Copy-DbaLogin and Export-DbaLogin transfer passwords. SQL Server 2019 refused: Invalid value given for parameter PASSWORD. Specify a valid parameter value. SQL Server 2022 CU27 accepted the hash, and login succeeded. Why 2022 knows it, we did not establish. We did not measure the way back with Copy-DbaLogin itself.
  • Password checks take longer. A single PWDCOMPARE check took about 340 ms on 2025 in the lab, compared with 70 to 90 ms on 2019 and 2022. A lab value, but noticeable in Test-DbaLoginPassword with many logins (section 5).

For a migration to 2025 with a possible way back, this means: keep the source's logins, as a script from Export-DbaLogin (section 5) or on the old instance. Logins that have already logged in on 2025 can no longer be copied back to 2019 by hash. 2025 also stores newly set passwords in the new format; for 2019, they would have to be set again. You can see the hash format of an instance like this:

-- Format of the password hashes per SQL login, first two bytes only (read-only)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT name,       CONVERT(varchar(6), CAST(LOGINPROPERTY(name, 'PasswordHash') AS varbinary(256)), 1) AS hash_formatFROM sys.sql_loginsOPTION (MAXDOP 1);

We measured this only with SQL Server 2025 CU9 in a Linux container. Because the hash is produced by SQL Server itself, we expect the same on Windows; it has not been tested there.

5 · Documenting permissions

Export-DbaLogin: a script with secrets

Export-DbaLogin writes all logins with server roles, server permissions, database users and database roles as a T-SQL script:

Export-DbaLogin -SqlInstance SQLSRV-01 -FilePath C:\lab\w2\login-export.sql

An excerpt for app_admin, SID and hash shortened:

IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'app_admin') CREATE LOGIN [app_admin] WITH PASSWORD = 0x0200… HASHED, SID = 0x…, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF, DEFAULT_LANGUAGE = [us_english]GOALTER SERVER ROLE [dbcreator] ADD MEMBER [app_admin]GOALTER SERVER ROLE [srv_monitor] ADD MEMBER [app_admin]GO…Grant CONNECT SQL TO [app_admin]  AS [sa]GOGrant VIEW ANY DEFINITION TO [app_admin]  AS [sa]GO

The command skipped accounts starting with NT … with a warning, as well as sa and the ##MS_…## logins:

Lab output
[09:16:49][Export-DbaLogin] Skipping ##MS_PolicyEventProcessingLogin##
…
[09:17:06][Export-DbaLogin] NT Service\MSSQLSERVER is skipped because it is a local machine name
…
[09:17:13][Export-DbaLogin] Skipping sa

The script documents well, but it is not a ready-made restore script:

  • It contains the password hashes. Whoever has the file can create the logins with the same passwords and attack the hashes offline. Treat it like a secret. With -ExcludePassword, it contains PASSWORD = ####### HASHED; in that form the script does not run.
  • IMPERSONATE is missing, as with copying. Get-DbaUserPermission shows it (next section).
  • Administrators are included. The command wrote the local administrator SQLSRV-01\Administrator into the script together with ALTER SERVER ROLE [sysadmin], and on Linux likewise BUILTIN\Administrators. On another server, the script thus grants sysadmin to an account that may not exist there or may mean something else. Check these lines before you run the script.
  • Logins without their default database fail entirely. We ran the script from SQL Server 2019 batch by batch on SQL Server 2017 without kasse. Every login with default database kasse was missing afterward (The database 'kasse' does not exist. Supply a valid database name. To see available databases, use sys.databases.), as was the membership in srv_monitor (Cannot alter the server role 'srv_monitor', because it does not exist or you do not have permission.). Only the logins with default database master were created, and login with the old password succeeded. Unlike Copy-DbaLogin, the script does not fall back to master.
  • Users with a name of their own: For kassierer, the script contains Grant CONNECT TO [alias_login] AS [dbo], that is, the name of the login instead of the user. A related problem with syncing database permissions is open in the project (#10837).

We did not test running the script generated on Windows.

Get-DbaUserPermission: complete, but not for read-only accounts

Get-DbaUserPermission lists roles and permissions at server and database level and also shows what Copy-DbaLogin and Export-DbaLogin leave out:

$r = Get-DbaUserPermission -SqlInstance SQLSRV-01 -Database kasse
$r | Where-Object { $_.Grantee -eq 'app_admin' -or $_.Member -eq 'app_admin' } |
    Format-Table Object, Type, Member, Securable, Grantee, Permission, State -AutoSize
Lab output
Object Type              Member    Securable Grantee   Permission          State
------ ----              ------    --------- -------   ----------          -----
SERVER SERVER LOGINS     app_admin
SERVER SERVER LOGINS     app_admin
SERVER SERVER SECURABLES app_admin SQLSRV-01 app_admin CONNECT SQL         GRANT
SERVER SERVER SECURABLES app_admin SQLSRV-01 app_admin VIEW ANY DEFINITION GRANT
SERVER SERVER SECURABLES app_admin app_leser app_admin IMPERSONATE         GRANT

The last line is the permission that got lost in the copy.

The command works with scripts from DISA's STIG checks. For this, it temporarily creates a schema STIG with views and functions in tempdb and removes it afterward. Help and source code say so, and a capture of the DDL events showed it in the lab.

That is why a read-only account cannot run the command. A login with VIEW ANY DEFINITION, VIEW SERVER STATE and CONNECT ANY DATABASE got 0 rows and this warning:

Lab output
[09:29:25][Get-DbaUserPermission] Failed to create or use STIG schema on SQLSRV-01 | User does not have permission to perform this action.
CREATE SCHEMA failed due to previous errors.

It needs the permission to create a schema in tempdb, so in practice sysadmin. For an audit account with read permissions, the command is not suitable; queries like the one in section 2 help there.

If the run aborts, the schema stays behind, according to the help. After our runs, it had been removed each time. You can check like this:

-- Leftovers of Get-DbaUserPermission in tempdb (read-only)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT COUNT(*) AS n FROM tempdb.sys.schemas WHERE name = N'STIG' OPTION (MAXDOP 1);

Test-DbaLoginPassword: only with a mandate

Test-DbaLoginPassword uses PWDCOMPARE to check the password hashes of SQL logins against an empty password, the login name and a word list of your own (-Dictionary):

Test-DbaLoginPassword -SqlInstance sql2019 -SqlCredential $cred

In the lab, without a word list the command found the test logins with an empty password and with the login name as password; with -Dictionary, it also found a login whose password was in the list; on SQL Server 2025, the same hits. The Password column contains the password found, in plain text.

So use the command only on systems you are responsible for, and with a mandate that explicitly covers password checks. The output does not belong in logs, tickets or reports; the list of affected logins is enough there.

6 · Checklist for a migration

  1. Record the source: permissions with Get-DbaUserPermission or the login query from section 2; store a script from Export-DbaLogin only as a secret.
  2. Restore the databases on the destination.
  3. Create user-defined server roles, for example with Copy-DbaServerRole (not tested by us).
  4. Copy the logins: without -NewSid; with several instances on one server, with -ExcludeSystemLogins. Like every command that writes, Copy-DbaLogin can be run with -WhatIf first; we did not record that output.
  5. Compare: run the login query on source and destination and add what is missing by hand.
  6. Check for orphaned users with Get-DbaDbOrphanUser; mapping and cleanup are covered in part 6.
  7. Destination SQL Server 2025: plan the way back before anyone logs in on the new server (section 4).

What we measured and what we did not

  • Measured on Windows (Nov 18, 2026): dbatools 2.9.1 under Windows PowerShell 5.1 on Windows Server 2022 with two instances of SQL Server 2022 RTM on the same server, Windows authentication. SQL logins, a local Windows user and a local Windows group; copy, comparison, restore, Export-DbaLogin, Get-DbaUserPermission with a capture of the DDL events. The VM had one vCPU; we therefore do not quote run times.
  • Also measured (Nov 18, 2026): dbatools 2.9.1 under PowerShell 7.6.8 in a Linux container against SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU9 in Linux containers: copy from 2019 to 2025, -Force, -NewSid, the hash change on 2025 with a cross-check on 2019 and 2022, running the login export on 2017, and Test-DbaLoginPassword. One run per variant, small test databases, throwaway lab logins only.
  • Source code and issues: We read the cited places in the 2.9.1 source, not the whole module, and searched the issues and pull requests on GitHub for reports on Nov 18, 2026.
  • Not measured: domain accounts and groups, two separate Windows servers, BUILTIN\Administrators between two Windows instances, -ExcludeSystemLogins, -KillActiveConnection, Copy-DbaServerRole, Sync-DbaLoginPermission, -UpdateExisting (only after 2.9.1), contained databases, case-sensitive collations, logins on Azure SQL Managed Instance, certificate-mapped logins, running the login export generated on Windows, -NewSid and SQL Server 2025 on Windows, and Test-DbaLoginPassword on Windows. We did not examine the format of the new hashes.

Further reading

More parts of the series