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

  • richlion2

    SSCrazy

    Points: 2479

    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:

    User mappings missing

    When I try the Script option I get an error message "nothing to script".

    User mappings 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>

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    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

  • Jeffrey Williams

    SSC Guru

    Points: 88537

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719896

    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: 2479

    Hi, yes, I understand that. Thank you

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

  • richlion2

    SSCrazy

    Points: 2479

    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 ***

     

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

  • richlion2

    SSCrazy

    Points: 2479

    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';

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

  • Jeffrey Williams

    SSC Guru

    Points: 88537

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719896

  • richlion2

    SSCrazy

    Points: 2479

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

    Richard

  • richlion2

    SSCrazy

    Points: 2479

    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

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

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

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