Erik Porter's Blog

Life and Development at Microsoft and Other Technology Discussions

News

    Attach Database Without Logins

    I was recently taking a SQL Server 2000 database and moving it over into SQL Server 2005.  I did it the old fashioned way and unfortunately the login that our application used to access the DB wasn't in the new SQL Server.  I was silly and tried adding the login to that server instance.  Fine.  Authentication with the DB still didn't work though and as many of you know, when I went to then delete the login from the DB itself I couldn't.  In SQL Server 2005 there is a system SP to call to automatically fix this.  Here's what I called...

    EXEC sp_change_users_login 'auto_fix', 'myLogin'

    That's it and everything was peachy again.  A lot of you have seen this already I'm sure, but for those of you who haven't, hope it saves you time.  This entry is mostly just so I remember it.  ;)

    Posted: Jan 26 2006, 09:04 PM by HumanCompiler | with 3 comment(s)
    Filed under: ,

    Comments

    foobar said:

    Hmm, for some reason I think that SP has been deprecated for SQL Server 2005.

    You can use the new DROP LOGIN and CREATE LOGIN commands to reattach users now.
    # January 27, 2006 1:33 AM

    Wim Hollebrandse said:

    I've used that same stored proc on many occasions when re-attaching a DB to a different database server (SQL2000).

    Using:

    EXEC sp_change_users_login 'update_one','username','loginname'

    The 'autofix' option tries to map the username to the same login name. The 'update_one' option lets you specify both username and loginname in cases where they might be different.
    # January 27, 2006 3:50 AM

    Paul said:

    Here's a general routine for those times when you have a whole bunch of logins to fix.

    declare @name varchar(100)

    declare @command varchar(100)

    declare filecursor cursor for select name from sysusers

    open filecusor

    fetch next from filecursor into @name

    while @@fetch_status=0

    begin

    use [database_name_goes_here];

    select @command = 'exec sp_change_users_login ''Auto_Fix'', ''' +@name + ''''

    exec (@command)

    fetch next from filecursor into @name

    end

    close filecursor

    deallocate filecursor

    # January 28, 2008 9:18 AM
    Leave a Comment

    (required) 

    (required) 

    (optional)

    (required)