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.
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-DbaLogintransfers 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 withsysadmin. -NewSidis for cloning. In a migration, it leaves the users orphaned.- Documenting permissions has side effects.
Export-DbaLoginwrites password hashes into the file,Get-DbaUserPermissionin practice needssysadmin. - 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, parameter | Effect |
|---|---|
Copy-DbaLogin -Source … -Destination … | transfers logins with SID, hash, roles, permissions and database users |
-Login, -ExcludeLogin | selection; without it, all logins except sa and ##…## |
-ExcludeSystemLogins | leaves out service and system accounts (according to the source code, section 3) |
-Force, -KillActiveConnection | drop and recreate an existing login; end open sessions |
-NewSid, -LoginRenameHashtable | assign a new SID, rename the login (for cloning, section 3) |
Export-DbaLogin | writes logins, roles and permissions as a T-SQL script; -ExcludePassword without hashes |
Get-DbaUserPermission | lists roles and permissions at server and database level |
Test-DbaLoginPassword | checks 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 databasekasse, member ofdb_datareaderthere.app_admin: member ofdbcreatorand of the user-defined server rolesrv_monitor, plusVIEW ANY DEFINITIONandIMPERSONATE ON LOGIN::app_leser.alias_login: its user inkasseis calledkassierer.gesperrt: disabled.pruefer:VIEW ANY DEFINITION,VIEW SERVER STATE,CONNECT ANY DATABASE.SQLSRV-01\dbt_winuser(local Windows user, default databasekasse) andSQLSRV-01\dbt_wingruppe(local Windows group, member ofsrv_monitor).
This is what the source looked like, read with the query further down (excerpt):
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 kasseThe 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 -WrapName 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_winuserFor the four logins with default database kasse, the same message also appeared as a warning. Everything else was "Successful". The comparison afterward:
| Property | Source | Destination after Copy-DbaLogin |
|---|---|---|
| SID of all copied logins | – | identical |
| Password hash of the SQL logins | – | identical; login with the old password succeeded |
gesperrt | disabled | disabled: Login failed for user 'gesperrt'. Reason: The account is disabled. |
Default database of app_leser, alias_login, gesperrt, dbt_winuser | kasse | master |
Server roles of app_admin | dbcreator, srv_monitor | only dbcreator |
Server role of the group dbt_wingruppe | srv_monitor | none |
Server permissions of app_admin | VIEW ANY DEFINITION, IMPERSONATE ON LOGIN::app_leser | only VIEW ANY DEFINITION |
Server permissions of pruefer | three permissions | all 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):
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' } -NewSidIn 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:
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:
| Instance | Hash after Copy-DbaLogin | after the first login | newly created login |
|---|---|---|---|
| 2019 CU32 (source) | 0x0200… (original) | unchanged | 0x0200… |
| 2022 CU27 | 0x0200…, same as source | unchanged | 0x0200… |
| 2025 CU9 | 0x0200…, same as source | 0x0300…, different hash | 0x0300… |
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 wayCopy-DbaLoginandExport-DbaLogintransfer 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 withCopy-DbaLoginitself. - Password checks take longer. A single
PWDCOMPAREcheck took about 340 ms on 2025 in the lab, compared with 70 to 90 ms on 2019 and 2022. A lab value, but noticeable inTest-DbaLoginPasswordwith 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.sqlAn 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]GOThe command skipped accounts starting with NT … with a warning, as well as sa and the ##MS_…## logins:
[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 saThe 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 containsPASSWORD = ####### HASHED; in that form the script does not run. IMPERSONATEis missing, as with copying.Get-DbaUserPermissionshows it (next section).- Administrators are included. The command wrote the local administrator
SQLSRV-01\Administratorinto the script together withALTER SERVER ROLE [sysadmin], and on Linux likewiseBUILTIN\Administrators. On another server, the script thus grantssysadminto 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 databasekassewas missing afterward (The database 'kasse' does not exist. Supply a valid database name. To see available databases, use sys.databases.), as was the membership insrv_monitor(Cannot alter the server role 'srv_monitor', because it does not exist or you do not have permission.). Only the logins with default databasemasterwere created, and login with the old password succeeded. UnlikeCopy-DbaLogin, the script does not fall back tomaster. - Users with a name of their own: For
kassierer, the script containsGrant 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 -AutoSizeObject 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 GRANTThe 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:
[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 $credIn 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
- Record the source: permissions with
Get-DbaUserPermissionor the login query from section 2; store a script fromExport-DbaLoginonly as a secret. - Restore the databases on the destination.
- Create user-defined server roles, for example with
Copy-DbaServerRole(not tested by us). - Copy the logins: without
-NewSid; with several instances on one server, with-ExcludeSystemLogins. Like every command that writes,Copy-DbaLogincan be run with-WhatIffirst; we did not record that output. - Compare: run the login query on source and destination and add what is missing by hand.
- Check for orphaned users with
Get-DbaDbOrphanUser; mapping and cleanup are covered in part 6. - 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-DbaUserPermissionwith 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, andTest-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\Administratorsbetween 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,-NewSidand SQL Server 2025 on Windows, andTest-DbaLoginPasswordon Windows. We did not examine the format of the new hashes.
Further reading
- dbatools: Copy-DbaLogin, Export-DbaLogin, Get-DbaUserPermission, Test-DbaLoginPassword (help with examples); open reports #10834 (permissions on logins) and #10837 (permissions synced by login name instead of user name).
- Microsoft Learn: What's new in SQL Server 2025 (Security section, PBKDF2).
- At PSG: dbatools overview, Part 1: Install and connect securely, Part 6: Orphaned users, Backup & Restore: basics, SQL Server on Linux for Windows admins (Windows logins on Linux).