Detach/attach and default database

  • We detached and attached some databases to put the log file on another drive. After this action, some applications gave errors because the logins they use have their default database set to the application's database (it's wrong i know). During the detach/attach action the default database changed because the dbid of the databases are different. The login table (sysxlogins) only stores the dbid. The only way to ensure nothing changes, is to attach the databases in the same order we created it, if possible (no deletes).

    Is this some bad referential integrity in the master database? or,

    Should the moving of the logs be done in another way?


    Kindest Regards,

    Gimli

    If you are a spineless creature, they can't break you

  • This is the standard way to move database files to different disks (or it is here).

    It sounds like you have some orphaned logins in the new databases, you should be able to troubleshoot and resolve the issue if you have a dig around in BOL for 'sp_change_users_login'.

  • I remember reading a discussion on dbid and attach/detach operations some time ago. The general conclusion was that SQL Server assigns the lowest unused id higher than 4 (reserved for system dbs). Of course, you don't have any control over this.

  • I have had this happen to me a couple of times.  I don't know why it happens sometimes but not all the time.

    My solution was to create a script called DefaultDB_reset.sql.  I list all of my users and their default database, using sp_defaultdb. When ever I detach/attach databases or stop/start SQL Server, I check the Security>Logins in Enterprise Manager. If the default databases are wrong, I just run my script to fix it.

    -SQLBill

  • As a practice I tend to set tempdb as the default database for all logins

     Just my $0.02

     


    * Noel

  • Noel,

    Good idea, but it doesn't always work. I have a third-party application (a major application where I work) that must have it's default database set for the 'user' database. If it's not, then it won't work (wanna guess why - the answer is at the end). My users can only read the data from the database and they do that via another application, so they don't have direct access to the data.

     

    The third-party app wasn't coded very well. I code all my scripts to have either USE dbname, or the FROM uses the three part naming convention.  The app doesn't do any of that, so it has to be running in the proper database.

    -SQLBill

  • Thank you all,

    These logins of mine are also from a third-party application. I think, it's a thing to keep in mind when you have to move database files. When you have logins that have their default database set to a user database: "script them or make note of them manually before you detach your databases".

    I looked at "sp_change_users_login", but i don't think that's gonna work in this case.


    Kindest Regards,

    Gimli

    If you are a spineless creature, they can't break you

  • sp_defaultdb [ @loginame = ] 'login' ,

        [ @defdb = ] 'database'

    HTH

    Dylan Peters
    SQL Server DBA

  • hi, i'm using this after restores:

    ------------------------------------------------------------

    declare @login sysname

    declare log_curs cursor for

    select name from sysusers where upper(name) 'DBO' and status=2

    open log_curs

    fetch next from log_curs into @login

    WHILE (@@FETCH_STATUS -1)

    BEGIN

    IF (@@FETCH_STATUS -2)

    BEGIN

    exec sp_change_users_login 'Update_One', @login, @login

    Print 'Login adapted: "' + @login + '".'

    if not exists (select * from model.dbo.sysusers where name = @login) BEGIN

    exec ('use model exec sp_grantdbaccess @loginame = ''' + @login + ''', @name_in_db = ''' + @login + ''' use master')

    Print 'Granted access for database model.'

    END

    exec sp_defaultdb @loginame = @login, @defdb = 'model'

    Print 'Set default database to "model".'

    END

    FETCH NEXT FROM log_curs INTO @login

    END

    CLOSE log_curs

    DEALLOCATE log_curs

    -------------------------------------------------------------

    Best regards
    karl

  • Hey karl,

    Will This code work for all the databases de-attached and attached.Can't we resolve the issue by using sp_change_users_login Stored Procedure? Also by setting the default database for all users to be tempdb.

    Thanks.


    Kindest Regards,

    Jeetendra

  • the script should work for all databases, and you can substitute model with tempdb.

    if you use sp_change_users_login with AUTO_FIX you have to overwrite your existing logins passwords, which may or may not be acceptable...

    normaly i'm using a second script to transfer logins and passwords from the original server, and only afterwards use the script above

    karl

    Best regards
    karl

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

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