Create Login Issue ... too many DBs assigned 1

[JayKusch]

First off we are dealing with SS2K.

We have a login has been given access to 2 DBs (which it should be).

If we execute spMShasdbaccess we see that the person has access to the 2 intended DBs BUT they also have access to another user DB as well as Master and MSDB which they should not have access to.

If we show the users for the DB that this particular login IS NOT suppose to have access to, he does NOT show up in the list as expected.

What are we missing here and how can/does this happen?



Thanks!


Thanks

J. Kusch

 

[mrdenny]

The guest account has access to the master and msdb database. It probably has access to the other user database as well. As the guest account is in the database, it is a member of the public role which then grants the user what ever rights are granted to the public role of that database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JayKusch]

Thanks Denny ...

SO that has been the area I have been focusing on for a bit.

Yes indeed the guest account IS in the DB in question BUT the guest account is also present in 10 other User DBs that are NOT being granted access to the login.

Hmmmm ... preplexed - lol

Thanks

J. Kusch

 

[mrdenny]

It's probably disabled in the other databases.

Check the sysusers table and see if the hasdbaccess column is 1 or 0.

An ALTER USER command will disable the user.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JayKusch]

VERY INTERESTING ... so that surely is the case.

SO let me get this straight in my head.

If I create a login and assign DB1 and DB2 as DBs the login can work with ...

I can then execute (after the login is finished creating of course) sp_MShasdbaccess which will not only show DB1 and DB2 BUT will also show any USER DB with an enabled "guest" account AS WELL AS Master and MSDB since they are required to have the guest account enabled?



Thanks

J. Kusch

 

[mrdenny]

Yep, it should as the user has db access via the guest account.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JayKusch]

SO ... is there any way to list/export the users that are found in the Public role of a particular DB?

I can see them but darned if I can find a way to get a list of them.

Thanks!

Thanks

J. Kusch

 

[JayKusch]

LMAO!!! ... NEVER MIND ... not thinking straight today.

Just needed the syslogins sp to do the trick

Thanks

J. Kusch