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?

2 Answers 2

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.

7
  • Thank you this showed me he was a member of the admins group.
    – oJM86o
    Nov 10, 2011 at 12:49
  • That'll do it. :)
    – mrdenny
    Nov 11, 2011 at 7: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, 2011 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, 2011 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, 2011 at 15:34
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?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .