[SOLVED]Migrating databases - error on login 4064 (cannot open default database)

Question

[SOLVED]Migrating databases - error on login 4064 (cannot open default database)

richlion2

SSCrazy

Points: 2484
More actions
December 23, 2019 at 3:15 pm
Go to Answer
#3709970
Hello,
I am trying to figure out how to create a script to generate Login mappings to databases. I've search a lot, plenty of cases and examples, but none seem to match my problem. Here a brief explanation:
1. I have daily backups of databases I copy to my new server. No Logins, etc.
2. I restore all my databases, no problems here.
3. I have a script that generates the following statement (1 user as an example):
```
CREATE LOGIN [Spaceman] WITH PASSWORD = 'MyPwd', DEFAULT_DATABASE = [MyDatabase], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF ;

use MyDatabase;
ALTER ROLE db_owner ADD MEMBER Spaceman;

use MyDatabase2;
ALTER ROLE db_owner ADD MEMBER Spaceman;
```
4. The one bit that is missing - in the properties of the user "Spaceman" the user to database mappings are blank. I need a script to generate those. I can't figure out how this would work:
https://stackoverflow.com/questions/8471124/t-sql-to-list-all-the-user-mappings-with-database-roles-permissions-for-a-login
In the end, when I try to login using SQL authentication the error comes up:

TITLE: Connect to Server
------------------------------
Cannot connect to ESCSRVSPACE\ESCSPACETEST.
------------------------------
ADDITIONAL INFORMATION:
Cannot open user default database. Login failed.
Login failed for user 'Spaceman'. (Microsoft SQL Server, Error: 4064)
ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
I suspect this error is not because the user "Spaceman" does not have a default database assigned, it has.
Which database (master, MyDatabase) should I look for and which system views can give me this information?
My Login "Spaceman" has more than one database mappings.
Thanks,
Richard

Here is a screen that show what I need:
When I try the Script option I get an error message "nothing to script".
I also found this:
https://dba.stackexchange.com/questions/96358/how-can-i-map-a-login-to-a-database-using-t-sql-not-ssms
But it's not working.

<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
Jeffrey Williams

SSC Guru

Points: 90304
More actions
December 24, 2019 at 4:10 pm
Answer
#3710257

You need to use something like this: ' + convert(varchar(85), Sid, 1) + '
You don't convert to a varbinary and then concatenate to a string - you need to convert the value to a string so it can be concatenated to the rest of the string.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Not sure I understand the problem fully, but is as simple as this? (I've done this from memory - forgive me if the syntax isn't 100%.)

USE MYDatabase;

IF NOT EXISTS (
    SELECT * 
    FROM sys.sql_logins
    WHERE name = 'Spaceman'
    )
EXEC ('CREATE USER Spaceman FROM LOGIN = Spaceman')
ELSE
EXEC ('ALTER USER Spaceman WITH LOGIN = Spaceman');

John

This reply was modified 5 years ago by John Mitchell-245523. Reason: Removed rogue quotation mark
This reply was modified 5 years ago by John Mitchell-245523.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 2

The users in the database were originally created on the source system - and were created with a specific SID. When you restore that database to a new system - the user still exists in the database with that SID.

When you create the new login on the new server it is created with a different SID - and that SID does not match with the existing user.

To create the login on the new system so it matches the user in the database, you need to create that login with the same SID that was used on the source system.

The easiest method is to script out the logins from the source system with the SID and using that to create the logins on the new system. Once that is done, restoring the database from the source system to the destination system will automatically synch the users in that database to the login because they now have the same SID.

The other option would be to query sys.database_principals to capture the SID of the user - then create the login based on that user SID. You need to place a check on the existence of that login and if it doesn't exist then don't try to create it.

I prefer the first approach because I manage all logins that are created and can insure any new logins are created on the source and destination if needed, but if you don't have control over that process I can see setting up the second process to capture new users/logins.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

What Jeffrey said. The logins need to match by name. You can script the logins differently or use the ALTER USER or sp_change_users_login to fix things

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-ver15

richlion2 SSCrazy Points: 2484 More actions · Answer 4

Hi, yes, I understand that. Thank you

richlion2 SSCrazy Points: 2484 More actions · Answer 5

Hello, I see this explains everything:

When you create the new login on the new server it is created with a different SID - and that SID does not match with the existing user.

So when generating the logins with SID's would be the best option, I tried that and some problems with it. I am also considering having a script to drop the users in the new location and recreating both the logins and DB users.

Thank you all for explaining.

Richard

**** And a Merry Christmas and a Happy New Year to all ***

richlion2 SSCrazy Points: 2484 More actions · Answer 6

Right, here where I am. I found numerous script that generate the "CREATE LOGIN .. SID= <sid>", some without SID, they don't seem to work and always something missing. I know I need this kind of statement, the SID is a VARBINARY, correct? So I cannot use it to concatenate using the + sign like here

So how can I convert the VARBINARY (SID) so I can generate this statement?

CREATE LOGIN [Spaceman] WITH PASSWORD = 0x020083xxxxxx73E3E05C765C91B4F5EF284A1126 HASHED, SID = 0xFDED87C67541A142902E384D0A3D34FD, DEFAULT_DATABASE = [MyDatabase], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

use master
select 'CREATE LOGIN [' + name + '] WITH PASSWORD = ' + '''' + 'Initial123' + '''' + 
', SID = ' + CONVERT( VARBINARY,Sid) + ', DEFAULT_DATABASE = [' + default_database_name + '], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF ;' + '
'
from sys.sql_logins
where default_database_name not in ('master','tempdb','model','msdb','testsample','ReportServer','ReportServerTempDB')
and name = 'Spaceman';

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

Use sp_helprevlogin

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

richlion2 SSCrazy Points: 2484 More actions · Answer 8

Thank you for the conversion hint, your example will help my understand this better.

Richard

richlion2 SSCrazy Points: 2484 More actions · Answer 9

I had the code for these two procedures from somewhere else, but it wasn't generating the correct SID. The link you gave me is a working one. I managed to recreate the login with the user mappings.

Great help, thank you all.

Richard