Sql Server access user to database after restore

Question

I've written this script to restore a database backup (from another server instance) and assign to a specific user an access to it. This script has run in powershell console

I've already got a valid login in target instance

# restores a full database backup to another database from source's latest full backup file in specified directory
# begin script configuration here
$TargetSqlServerInstance = "MyInstance"
# target server instance 
$TargetDb = "targetdb"
# target database 
$BackupDir = "C:\backupDB\targetdb"
# directory / share where backups are stored 
# compatibility level to set target database to (2019=150, 2017=140, 2016=130, 2014=120, 2012=110, 2008/2008R2=100, 2005=90, 2000=80, 7=70) 
# end script configuration here
# latest full backup file name is dynamically determined and appended to backup directory
$LatestFullBackupFile = Get-ChildItem -Path $BackupDir -Filter *.bak | Sort-Object 
LastAccessTime -Descending | Select-Object -First 1 
$FileToRestore = $BackupDir + '\' + $LatestFullBackupFile
# kill any connections in target database
$KillConnectionsSql=
"
 USE master
 GO
 ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 GO
 USE master
 GO
 ALTER DATABASE $TargetDb SET MULTI_USER
 GO
 USE master
 GO
"
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql 
r
# restore
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile 
$FileToRestore -ReplaceDatabase
# end restore
GO
USE [targetdb]
GO
CREATE USER [myuser] FOR LOGIN [myuser]
GO

My trials

• When I've run this script under planned operation under Windows OS, the restore it's OK, but I can't access to database with user.

• I've tried to execute by Sql Server Management Studio the script about create user and association with database and works fine

• I've tried to create a Maintenance Planned with SSIS and the the result about association of user doesn't work

• I've tried to separate in two steps under Maintenance Planned, the first for restore, the second for user creatiuon but the second step fails

• I've tried to create another Maintenance Planned with one subplan with two serialized activies but nothing.

The job has always a success status, but simply, I can't link my user to my target db

EDIT

I've found the user under Security folder of targetDb but, if I check under global Security folder the login has no checkd the access to that database. So I can put it manually or with the separate script

• J.D.: What does "I can't access to database with user" mean?...what are you trying to do with that user? From your code the user should exist in the database (you should be able to confirm by looking under the Security node of that Database in SSMS), but has virtually almost no permissions being granted to it.
• Dominique Boucher: Is this user already present in the database that is provided in the backup ? (if you see it under "security" under database but you do not see it in the login "database", it's usually an orphean user. It could be a permission issue with the account running the scripts. Provide the error when the permission script is in step 2, this could help.

Answer 1

It seems the user is an orphaned user ie it exists in the database but is not connected to a server login. This is because users are mapped to server logins by SID not by name.

You can reattach it using this

ALTER USER [myuser] WITH LOGIN = [myuser];

---

Having said that, I would strongly recommend you use the excellent DbaTools Powershell module. This can do all of what you want quite easily.

For example, Repair-DbaDbOrphanUser can repair all orphaned users in a database.

$TargetSqlServerInstance = "MyInstance"
# target server instance 
$TargetDb = "targetdb"
# target database 
$BackupDir = "C:\backupDB\targetdb"
# end script configuration here
# latest full backup file name is dynamically determined and appended to backup directory
$LatestFullBackupFile =
 Get-ChildItem -Path $BackupDir -Filter *.bak |
 Sort-Object LastAccessTime -Descending |
 Select-Object -First 1;
Set-DbaDbState `
 -SqlInstance $TargetSqlServerInstance `
 -DatabaseName $TargetDb `
 -SingleUser;
Restore-DbaDatabase `
 -SqlInstance $TargetSqlServerInstance `
 -DatabaseName $TargetDb `
 -Path $LatestFullBackupFile;
Set-DbaDbState `
 -SqlInstance $TargetSqlServerInstance `
 -DatabaseName $TargetDb `
 -MultiUser;
Repair-DbaDbOrphanUser `
 -SqlInstance $TargetSqlServerInstance `
 -DatabaseName $TargetDb;