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?