1

I just get a problem with a customer's SQLServer. I'm reading articles related to schema, owners and permissions, but I can't get it out.

I have two DB : DB1 and DB2. I have a schema, which has the same name of it's owner (user1, which has "user1" as default schema). This schema exists on both databases, user1 is mapped as owner on both databases.

So, some of my tables are in this format : user1.table1, user1.table2, dbo.table3...

DB1 is not used anymore : I turned it offline. Just after that, the tables [user1].[tableXX] from DB2 were not usable... Turning back DB1 online solved the problem.

How can this happens? How can I turn DB1 offline without blocking DB2?

Thanks in advance!

Improve this question
8
  • Precision : user1's default database is... DB1.
    – Patator
    Commented Sep 2, 2014 at 13:20
  • What do you mean "not usable"? Is there some error message returned when trying to access these user1 tables?
    – Hannah Vernon ♦
    Commented Sep 2, 2014 at 13:21
  • Nothing on the SQLServer's side, but the PHP test pages returned : "Cannot open user default database. Using master database instead. (severity 11)" then "General SQL Server error: Check messages from the SQL Server (severity 11)"
    – Patator
    Commented Sep 2, 2014 at 13:28
  • 1
    Sounds like your error will be fixed by just changing the default db to DB2. And it has nothing to do with schemas, but everything to do with the user under which the PHP page runs.
    – Reaces
    Commented Sep 2, 2014 at 13:29
  • 1
    You want to use limited permissions as much as possible. Instead of giving the user permissions on the master DB to log on, change the default database to DB2.
    – Reaces
    Commented Sep 2, 2014 at 13:34

1 Answer 1

Reset to default
4

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists.

exec sp_defaultdb @loginame='user1', @defdb='DB2'

OR

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.