Question to command Add-DbaDbRoleMember

[stj-fr]

Hello,

sorry for my bad english. I've read the docs but probably doesn't see something:

What I want to do:
(like this: https://www.sql-aus-hamburg.de/tsql2sday-94-daily-database-copy-using-powershell-dbatools/)
Transfer a database by script, restore it automatically and change/clean user it works until point 5...

1. Transfer Backup via ftps -> ok, works
2. restore with dbatools -> ok, works
3. clean orphaned logins -> ok, works
4. set-dbadbowner to sa -> ok, works
5. add existing sql user to db_owner role with command -> failed

PS C:\> Add-DbaDBRoleMember -SqlInstance localhost -Role db_owner -User sqluser -Database mydb
WARNING: [10:22:08][Add-DbaDbRoleMember] User sqluser does not exist in [MYDB] on [SERVER]

(I tried it with -SqlInstance SERVER, but same error)

Any hint or help are very appreciated

I added the sql user with SSMS and read it with Get-DbaDbRoleMember

PS C:\> Get-DbaDbRoleMember -SqlInstance SERVER -Database mydb

ComputerName : SERVER
InstanceName : MSSQLSERVER
SqlInstance  : SERVER
Database     : MYDB
Role         : db_owner
UserName     : sqluser
Login        : sqluser
SmoRole      : [db_owner]

Thank you and kind regards

P.S. SERVER and mydb are not the real names

[stj-fr]

Hello,
I've found it my fault, I have to issue first this command to map the user to the database:

New-DbaDbUser -SqlInstance SERVER -Database mydb -Username sqluser -Login sqluser

ComputerName       : SERVER
InstanceName       : MSSQLSERVER
SqlInstance        : SERVER
Database           : MYDB
CreateDate         : 02.08.2021 11:48:05
DateLastModified   : 02.08.2021 11:48:05
Name               : sqluser
Login              : sqluser
LoginType          : SqlLogin
AuthenticationType : Instance
State              : Existing
HasDbAccess        : True
DefaultSchema      : dbo

And than Add-DbaDBRoleMember works....

[andreasjordan]

If you have SQL Logins in source and destination, take care that the SID match. So get the SID from source and create the SQL Login at the destincation with the same SID. Then you don't have orphaned users in the databases to take care of.