Error copying sql permissions with Copy-DbaLogin when the database user and server user does not match.

jfoudy commented 3 months ago

Verified issue does not already exist?

No, I did not search

What error did you receive?

The TargetServerVersion is because this was ran against a SQL 2022 instance and some of the databases are Version160 and the script doesn't appear to be 2022 compatible.
The ExecuteNonQuery failed for Database 'AdventureWorks' has to do with the database user and server user login names not matching.

WARNING: [13:43:06][Update-SqlPermission] Failed to add Domain\NewUser (login: Domain\NewUser) to AdventureWorks on Hostname. | 'Domain\NewUser' is not a valid name becaus
e it contains invalid characters.

Exception setting "TargetServerVersion": "Cannot convert null to type "Microsoft.SqlServer.Management.Smo.SqlServerVersion" due to enumeration values that are 
not valid. Specify one of the following enumeration values and try again. The possible enumeration values are 
"Version80,Version90,Version100,Version105,Version110,Version120,Version130,Version140,Version150"."
At C:\Program Files\WindowsPowerShell\Modules\dbatools\allcommands.ps1:84422 char:21
+ ...             $scriptOptions.TargetServerVersion = [Microsoft.SqlServer ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'AdventureWorks'. "
At C:\Program Files\WindowsPowerShell\Modules\dbatools\allcommands.ps1:84430 char:21
+                     $destDb.ExecuteNonQuery($userScript)
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

Steps to Reproduce

Copy-DbaLogin `
    -LoginRenameHashtable @{"Domain\OldUser" = "Domain\NewUser"} `
    -Source HostName`
    -Destination HostName`
    -Login 'Domain\OldUser' `
    -ObjectLevel `
    -NewSid `
    -Verbose -Force

Please confirm that you are running the most recent version of dbatools

2.1.11

Other details or mentions

The root cause is because the server principle is not being matched with the database principle by sid. The database name for the user is "OldUser", but the name in sys.server_principles is "Domain\OldUser". The script was able to copy all permissions for databases where the names matched, but for the one that the names didn't match, it would fail.

This query will show you a list of all the users on the database and server level:

` DECLARE @Results TABLE ( type varchar(100), DatabaseName varchar(100), db_username varchar(100), server_username varchar(100) )

DECLARE @command varchar(1000) 
SELECT @command = '
    USE [?] 

    select ''Database Level'', DB_Name() ''Database'', d.name db_username, s.name server_username
    from sys.database_principals d
    JOIN sys.server_principals s ON d.sid = s.sid
    where s.name like ''%domain\oldusername%''
' 

insert into @Results
EXEC sp_MSforeachdb @command

` Sample Results (csv):

Database,db_username,server_username DatabaseOne,BobTheUser,DOMAIN\OldUser DatabaseTwo,DOMAIN\OldUser,DOMAIN\OldUser

TO ADD A USER TO A DATABASE TO RECREATE THE ISSUE JUST RUN THIS COMMAND (edit info of course, and make sure the "SomeOtherName" and "OldName" are different, "OldName" is the login name on the server side users):

USE [Test] GO CREATE USER [SomeOtherName] FOR LOGIN [OldUser] WITH DEFAULT_SCHEMA=[dbo] GO

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name : ConsoleHost Version : 5.1.17763.5576 InstanceId : 45458c0a-21de-45c9-ba40-79e2f3791d47 UI : System.Management.Automation.Internal.Host.InternalHostUserInterface CurrentCulture : en-US CurrentUICulture : en-US PrivateData : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy DebuggerEnabled : True IsRunspacePushed : False Runspace : System.Management.Automation.Runspaces.LocalRunspace

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU3) (KB5024396) - 16.0.4025.1 (X64) Mar 13 2023 19:10:08 Copyright (C) 2022 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

PSChildName Version

v2.0.50727 2.0.50727.4927 v3.0 3.0.30729.4926 Windows Communication Foundation 3.0.4506.4926 Windows Presentation Foundation 3.0.6920.4902 v3.5 3.5.30729.4926 Client 4.8.03761 Full 4.8.03761 Client 4.0.0.0

HCRitter commented 3 months ago

Hi,

as far as I´ve checked, the ENUM in $DestServer, later in the script: [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::$scriptVersion is not a correct field in the SMO!

Namespace: [Microsoft.SqlServer.Management.Smo] Assembly: Microsoft.SqlServer.Smo.dll Package: Microsoft.SqlServer.SqlManagementObjects v160.2004021.0 (https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo?view=sql-smo-160)

So it's not caused by DBATools, more it's that the [Microsoft.SqlServer.Management.Smo] is outdated in version 160 for that ENUM.

jfoudy commented 3 months ago

DestServer

Ok, how would I fix that then?

The main issue really for this ticket is the Login Names must match between Server and Database.

andreasjordan commented 3 months ago

I can reproduce the issue, will try to provide a fix...

andreasjordan commented 3 months ago

If name of user and login match, there is not error, but the new user is added and the old user is still present in the database. I think the old user should be dropped. Or am I wrong?

dataplat / dbatools