Domain Account Access

Question

This happens on both SQL2000 and SQL2008

We have "Domain\Domain Users" group added and it is mapped to a few databases - say DB1, DB5 and DB10.

On SQL2000, it doesn't show it has any Server Roles under Login Properties. However, if you click on User Mapping and each db (DB1, DB2, etc); it does show it has Public in Database Role Membership.

On SQL2008, under Login Properties, Server Roles, Public is checked and in Login Properties, User Mapping - it shows Database Role Membership has Public for each db (DB1, DB2, etc).

Of course, on the DB1, DB5 and DB10, "Domain\ABC" has additional database roles.

Issue:

When I log in through SSMS2008 with a "Domain\abc" (this username is not in DB, Security, Logins tree) - it's able to login. I figured, it's getting the access through the "Domain\Domain Users".

Issue 1: I was able to query master..sysdatabases to get a list of available databases with "Domain\ABC" username. Is that normal? If not, how do I disable that?

Issue 2: I was able to issue "USE DB9" (notice it's NOT one of the DB mapped for "Domain\Domain Users") and do query from any tables in DB9. I think this is a major security issue - How do I disable that?

Like I said, this happens in both SQL2000 and SQL2008.

Thanks

Answer 1

Issue 1: That's normal. All logins have access to the master database (unless they are contained DB users in SQL Server 2012). By default, all users can see the existence of all the databases. You can block that by executing DENY VIEW ANY DATABASE TO <login_name>; But then the login won't be able to see any databases, including databases that they can access. We have found that blocking such information causes a lot of problems for most users. Which is why the product ships that way.

Issue 2: Permissions for users are aggregated. So the user must be picking up permissions from somewhere. Perhaps the Database user is a member of a Windows group that has a login which has access. The user might be a member of several groups, and aggregate access permission from several logins. For some queries to investigate, see the topic in the TechNet SQL Server Wiki Effective Database Engine Permissions http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx

The SQL Server 2000 permission system was different. I don't have one handy to check that.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Issue 2 : You would be right. Checked AD and that particular account is a member of a lot of groups, more groups than I thought. And one of the groups does have access to all dbs.

Thanks!