| Author |
Topic |
|
matthisco
Starting Member
48 Posts |
 Posted - 2010-01-21 : 05:37:28
|
| Hi, Can someone please tell me how I can set access for a user to edit a single Database?I've added their windows login to the security > logins, set the appropriate database as their default database.However for some reason they can open all databases on the server.Can anyone help?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:13:10
|
| Sounds like they have also been Granted a Server Role - such as sysadmin |
 |
|
|
matthisco
Starting Member
48 Posts |
Posted - 2010-01-21 : 06:16:11
|
| Thanks for your reply. Which role is it they require just to edit a single Db? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:41:31
|
| I think PUBLIC Server Role should be fine, but that's from memory. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-21 : 08:54:10
|
| Have you checked to see they are mapped to only that one database (that's assuming they don't have server roles) ?Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
matthisco
Starting Member
48 Posts |
Posted - 2010-01-25 : 11:05:31
|
| Thanks for all your replies.I have added the user login to main security > logins. I've added the default database in the user mappings and given him the server role public.Then, in the database > security > users > he has securityadmin, owner, ddladmin, bakupoperator, accessadmin. These boxes are greyed out and I cannot remove these persmissions.I just need to give him access to a single database, so he can create/ modify tables.When he tries to open the Db in management studio he gets the following error:ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)-----------------------------The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-25 : 11:50:24
|
| apparently the user owns schemas and or database roles.what you should have done was not assign them to any server roles.create the login and assign default db. then assign them any database roles you want them to have in that db. i would start with just db_datareader, and if they need write access db_datawriter.also, db_owner implies all of the other roles. i would strongly suggest not granting db_owner. |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-01-25 : 12:52:32
|
| 1. go to security, login, select login, right click and select properties.2. In default database, change it to master.3. select user mapping on right hand side.4. under database column, select your required database.5. under databsase role membership, select db_datareader and db_datawriter6. log off the user and login again. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-25 : 13:20:17
|
| never, never, never make users default database master. also, no need to log user off and back on. |
|
|
|
matthisco
Starting Member
48 Posts |
Posted - 2010-01-26 : 04:57:29
|
| Thanks very much for your reply. Just to confirm, do I add the user to the main security or the security of the individual db?At the moment the user in in both.Thanks again |
|
|
|
matthisco
Starting Member
48 Posts |
Posted - 2010-01-26 : 05:02:29
|
| Also, when I go into server roles for the user, there is no data reader or data writer I get these options, could you please tell me which one I need?bullkadmindb creatordiskadminproecessadminpublic (checked)securityadminserevradminsetupadminsysadmin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 05:32:56
|
| Those are Server Roles (Public should be enough). The Date Read / Writer are Database roles - i.e. you'll find them on the specific database, not per-server. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-26 : 08:27:49
|
| You're looking at the permissions opn the login . On SSMS , expand open Users,right click on Properties of one of the users , where you will see the speific user permissions on that specific dbJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-26 : 08:46:05
|
quote:
Originally posted by matthisco
Thanks very much for your reply. Just to confirm, do I add the user to the main security or the security of the individual db?At the moment the user in in both.Thanks again
in SSMS, open the login and go to the user mapping page. |
|
|
|
matthisco
Starting Member
48 Posts |
Posted - 2010-01-26 : 08:58:21
|
| I've got it working!Thankyou very much, really appreciate your help |
|
|
|
|
user persmissions for database