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?
Best Answer
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.
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.