February 16, 2005 at 2:32 am
I've got a database server that is currently exhibiting some odd problems. Running SQL Server 2000 on Win2k SP4. Latest updates to SQL server have been applied. We've got approximately 1800 databases on this system.
Here's the issue. Every now and again it gives an error when logging in via Enterprise Manager or WebSQL. The error is as follows :
<user> is not a valid user for <database name>
I've removed the usernames/database names for annonymity. The database name does not change, it's always the same database. The user field changes depending on who tries to log in. No additional privileges have been added/removed from the database in question.
The wierdest part about all of this is the temporary solution that restores access. To restore access, all I have to do is display the list of databases via Enterprise Manager (logged in as SA).
Anyone have any ideas?
February 16, 2005 at 8:35 am
I think the problem is the fact that your login has a default database that the user has no access. The best practice is to have all login default to pubs database and when you add them to be a user of any database it works just as fine. You also have the flexibility to remove database without affecting user as well.
mom
February 16, 2005 at 12:35 pm
There is no "default database" for the server. Each database has unique users, and there are no databases created that have no users. This database server has been in production for nearly two years, but we just started having this problem within the last 2 months.
February 16, 2005 at 9:18 pm
How many logins are there on the server - a lot or a few. Doers it always happen to the same database. If so the is it one of the last ones created. I suspect that the large number of databases on this server is causing the issue. Is there any login timeout period set?
February 16, 2005 at 9:41 pm
I'm not sure of the actual number of logins, but it's most likely near 5-600. The same database is always the one that generates the error, but it doesn't matter which user account tries to log in.
IE - login "1" owns databases 1,2,3,4,5
login "2" owns databases 6,7,8,9,10
login "3" owns databases 11,12,13,14,15
Database 14 is always the one that is giving the error, no matter if it is login 1,2, or 3.
February 17, 2005 at 6:37 am
How busy is this database? Does the error occur when the most activity is occuring? I know you can have login errors in SQL Agent when to many job try to start at the same time - they block each other from checking the login security in Master. Maybe you have something simliar to this going on but with the DB security tables in that particular database.
February 17, 2005 at 7:58 am
Was the problem database created on the server, or was it attached after being created elsewhere? Maybe there is a SID mismatched between the sysusers table in the problem database and the syslogins table in master.
Run the following script in query anaylzer. Make sure the problem database is the current database.
USE <your database>
select u.sid as user_sid,
log.sid login_sid,
u.name as username, u.uid
from sysusers u
LEFT OUTER JOIN master.dbo.syslogins log ON u.sid = log.sid
where u.uid between 1 and 16383
and log.sid is null
and u.name <> 'guest'
Do you get any results?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply