July 15, 2005 at 12:03 pm
We recently created 2 new databases during the installation of some software, and now one of those new databases is showing up as the "default" database in the Query Analyzer window for many users, but I can't see why. It's not the real default login when I look at logins in Ent Mgr. I checked the logins via EM security, and only 1 user has explicit authority to those 2 databases (I'll call them NEWDB1 & NEWDB2).
To test this further, I gave SQL access to a windows account called TEST2, and give TEST2 access to only 1 of our regular databases. Yet, when I log on to my PC as TEST2, open Query Analyzer, NEWDB2 is my default database, and I can browse & query the objects in it.
These are all windows accounts with SQL permissions via Active directory.
( I just edited this post for clarity)
Any thoughts ??
SQL2000 SP4, Win2000
July 15, 2005 at 12:27 pm
You can assign a default database even if the user doesn't have explicit access to it. Case in point: master.
Because of the permissions set for the public database role, you can browse to see what objects are present if you have access to the database. But querying tends to be a different story.
Is the guest account enabled for that database?
If so, does the guest account or the public role have explicit permissions to query the objects in question?
If no to those to questions, do any Windows groups have access to the database?
If so, are the users (assuming they aren't SQL Server logins... your post indicates that they are, but just in case) members of any of those groups?
K. Brian Kelley
@kbriankelley
July 15, 2005 at 12:29 pm
Hi,
You have to explicitly set the default database. It is different then giving an access to the database. In EM, login properties Default Database is on General tab and Database Access is on Database Access tab.
I think if you did not set it explicitly then your new SW has a procedure or trigger somewhere to give new logins NewDB2 as default Database
Yelena
Regards,Yelena Varsha
July 15, 2005 at 1:22 pm
No, the guest account is not enabled for NEWDB1 & NEWDB2
Yes, a Windows Group does have access to NEWDB1 & 2, and separate Windows groups have access to our other DBs, but there shouldn't be any cross-over between them.
July 15, 2005 at 1:40 pm
Are the logins you're testing with Windows logins or SQL Server logins?
K. Brian Kelley
@kbriankelley
July 15, 2005 at 1:55 pm
These are all windows accounts with SQL permissions via Active directory and Global groups.
July 15, 2005 at 1:57 pm
Are the two logins both in a group that has access to the databases in question?
K. Brian Kelley
@kbriankelley
July 15, 2005 at 2:16 pm
When NEWDB1 & 2 were added via the software install, I created a new global group for it and put 1 new windows login in that group, and gave that group access to just the new DBs.
Suddenly, other users, in other, completly unrelated groups got access to NEWDB1 & 2, without me giving any of them rights to those new DBs, and NEWDB1 shows up in their Query Analyzer database pulldown window.
July 15, 2005 at 2:36 pm
You may want to check the users defined in the MODEL database as well, just in the event the windows group is defined as users there.
July 15, 2005 at 2:40 pm
Check the access on NEWDB1 ad NEWDB2. As indicated, there may have been something in Model. Or something else may have gotten added.
K. Brian Kelley
@kbriankelley
July 18, 2005 at 10:03 am
I couldn't find anything abnormal.
I detached NEWDB1 & 2, and got login errors when I tried to reconnect with my own loging & another one. I logged on as "SA" & set the default databases to master for some of the logins (including my own) and that seemed to fix things. Something, somewhere must have been corrupted, but I didn't figure out what.
Thanks for all your help !
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply