Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
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 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 '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

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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.