Deleted database, user default not reset?
-
Recently we migrated a database to a new server, and deleted the old database.
A number of users still required access to other databases on the original server. Some of those users originally had their default database set to the database which was deleted.
Some users have since reported a logon error, that their default database was not found.
I have a couple of problems with this error.
When a database is deleted, the default database for any associated users is supposed to be set to Master. Looking at the sysxlogins table, this appears to have happened.
We subsequently used the sp_defaultdb procedure to force the default database to Master for all users. The problem still occurs. Again, the default database is showing correctly in the sysxlogins table.
Any ideas on where to look next?
-
Two things:
1) Why did you set the default db to Master and not the actual database that the users will be using ?!
2) Here's something from BOL that should help:
"After sp_defaultdb is executed, the login is connected to the new database the next time the user logs in. However, sp_defaultdb does not automatically give the login access to that database. The database owner (dbo) must give database access to the login through sp_grantdbaccess, or there must be a guest user specified in the database."
**ASCII stupid question, get a stupid ANSI !!!** -
Thank you for your reply, Sushila.
The default database is (and should be) set to Master in this case, because most of our users require access to more than one application database, and there is no natural default.
Thanks for the pointer to the documentation regarding access. I'll check this out.
-
... When a database is deleted, the default database for any associated users is supposed to be set to Master. Looking at the sysxlogins table, this appears to have happened. ...
Nope
You will have to set the new default-db for each user.
This testscript will show it :
-- This script works with systemobjects wich should not be encouraged to be used directly !
EXEC sp_addlogin @loginame ='ALZDBATEST' , @passwd = 'mypassword'
go
select name, dbid
from master..sysxlogins
where name = 'ALZDBATEST'
-- points to dbid 1 = master
go
create database MyNewDefaultTestDB
go
exec sp_defaultdb @loginame = 'ALZDBATEST' , @defdb = 'MyNewDefaultTestDB'
go
select name, dbid
from master..sysxlogins
where name = 'ALZDBATEST'
-- points to dbid ... = 'MyNewDefaultTestDB'
go
drop database MyNewDefaultTestDB
go
select name, dbid
from master..sysxlogins
where name = 'ALZDBATEST'
-- still points to dbid ... = the dropped 'MyNewDefaultTestDB'
go
-- Cleanup when done !!!
-- EXEC sp_droplogin 'ALZDBATEST'
Test it !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply