2

I've got a sql server instance with about 50 databases on it. A developer, call him Joe, wants access to one existing database. So I went ahead and created a new log on account in sql server 2005 management studio and in the user mapping section I specified that he has access to this one database.

Upon logging on we noticed he can get to ANY of the databases and open all the tables, drop tables, etc.

How do I limit him without making him the db_owner of the database? Does it have something to do with "Public" access under roles?

Here is the steps I took went into management studio and went to security->logins and right clicked and added a new "Log on". Selected "windows authentication". In the user mapping tab I selected the database that he should have access to thinking this would give him only access to this database.

Then I fired up management studio and logged in as him (windows authentication) and he can open and see all tables from all databases? But I dont want him to access all of these?

oJM86o
  • 143
  • 8

2 Answers2

4

Sounds like Joe is a member of a domain group that has more rights on the SQL Server than you want, or you've been messing with the rights to the public roles in the other databases on the server. In either case you'll need to find out where the extra rights are coming from in order to fix them.

You can use the stored procedure xp_logininfo to see what domain groups Joe is able to access the database through.

exec xp_logininfo 'YourDomain\JoesUserName'

If that doesn't point you in the right direction you'll need to look into the rights granted to the public role in the other databases.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • Thank you this showed me he was a member of the admins group. – oJM86o Nov 10 '11 at 12:49
  • That'll do it. :) – mrdenny Nov 11 '11 at 07:37
  • just a question though, assume I wanted this guy to be a domain admin, but I wanted to use windows authentication and still restrict his permissions, is that possible? – oJM86o Nov 11 '11 at 18:07
  • Yes, you'd need to remove the BUILTIN\Administrators group from the sysadmin list (after adding yourself and that other DBAs as members of the sysadmin group though another local or domain group). There is nothing that says that domain admins must be SQL Admins, and usually they shouldn't be. – mrdenny Nov 17 '11 at 10:47
  • Where do I find this? Is this within management studio or are you talking about within the windows server admin groups. – oJM86o Nov 17 '11 at 15:34
  • aha I think I understand and found it. Within the server roles was sysadmin and ironically it had our entire domain admins group on there. So now I understand I need to delete that and add only the correct admins that should see the databases there. Thanks so much I learned a lot today... – oJM86o Nov 17 '11 at 15:39
  • Yep. Don't forget to add the new group first, then remove the old group. By default the BUILTIN\Administrators group was added as an admin for older versions. Newer installs don't have this problem by default. – mrdenny Nov 18 '11 at 09:59
2

They have more rights that what you expect then.

It isn't Windows auth, but permissions.

To see in all databases means they have "sysadmin" rights at the server level or have access via a Windows Group. Normally someone can see all database but will get an error on trying to expand it. No error = some permissions assigned.

And "DROP" means sysadmin if it applies to all databases.

Is "Joe" a member for a SQL Admin group?

gbn
  • 6,079
  • 1
  • 18
  • 21