Re: Sql users are redirected to wrong databases

From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 12/20/04 

Date: Sun, 19 Dec 2004 23:18:52 -0600

Where the databases recently detached, perhaps for maintenance or to move to
another drive?

The defaultDB setting is by dbid; whenever a database is created or
attached, it assumes the next available id. If they are sequentiallyfilled,
the new database will take the next highest available; however, if there is
vacant id due to a recent removal or detach, then the new database will
assume that one. This forces a user to be assigned to a different database
(same id) as their defaultDB.

Sincerely,

Anthony Thomas 

-- 
"oj" <nospam_ojngo@home.com> wrote in message
news:uFzBcBk5EHA.3840@tk2msftngp13.phx.gbl...
Take a look at sp_defaultdb in book online. This allows to explicitly set a
login to a specific database.
Also, try to explicitly define the database context in the connection
string.
e.g.
database=casts;
"Murtix Van Basten" <murtix@murtix.com> wrote in message
news:41c63cee_3@alt.athenanews.com...
> Hi,
>
>   I am not sure if this would be the right heading for the question but,
> this is something I haven't faced before.
>
>   In a mssql 2000 sp3 server has around 30 users and 30 databases. Each
user
> is a dbowner of an individual database. There is not any user owns two or
> more database in the server.
>
>   Recently, the users are complaining about, when they are try to access
to
> the server via ent-manager or via a connection string, the server is
making
> them try to connect to a database that is not they are supposed to connect
> to. For example:
>
> username:                      database:
> ----------------------------------
> garfield ------------>     cats
> lessie  -------------->    dogs
>
> This is the way it supposed to be. But when garfield wants to manage his
> "cats" database, he ends up connecting to the "dogs" database, even though
> he specifies that he wants to connect to the "cats" database. Naturally,
he
> gets a permission error when he connects to the "dogs" database, becase
> garfield doesnt have any permit on the "dogs" database.
>
> I dont know how did it happen. I have checked the databases and users at
the
> server, all are look fine and the settings are correct. I could not see
any
> problems on the sql server logs. I dont know if it is a security problem
or
> something screwed up in the database server. If anyone has any idea about
> this issue, please share with me.
>
> Thanks.
>
> Murtix.
>
>

Relevant Pages

  • Create SharePoint Portal failed.
    ... One mentioned ensuring that SQL Server uses a case ... 13:55:40 Service database server is 'USDC-JOHRIV'. ... Update dbo.propertylist set DisplayName = N'Last name' ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Detach and Attach for Backup and Deployment?
    ... detach + attach is a viable solution... ... backup your distribution database and restore it on user's server.. ... Moving SQL Server Databases to a New Location with Detach/Attach ...
    (microsoft.public.sqlserver.msde)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Web Developers - Happy Hearts And HDTV! - Lockergnome
    ... Certificate on your MSIIS Web server. ... getting data from a database is only half the problem. ... Zend recently started a series about building rock solid code in PHP. ... which provides bulk database conversion. ...
    (freebsd-questions)
  • Re: transfer db from home to work.
    ... I would use detach / attach for this. ... Or, have the database live in a place accessible from both work and home, ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)