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?
-
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.
-
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 -
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