Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rocky1118
Starting Member
2 Posts |
 Posted - 2007-08-29 : 02:24:12
|
| Hi, Am new to sql server. please help me.I have X, Y , Z schema's with 3 tables each in a database. I have to make user groups A and B. I need to give permissions to A to select only X & Y schema's, and B to select only Z schema's. Any user who try to access this database should be asked for ID and password, on success he should be able to access only to the schema's which are under that user group/role. I also have to create one administrator role who can create the users for these user groups.Please can anyone tell me how can i do it.Thanks a lot in advance |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-30 : 01:30:30
|
| Create two db roles in the db and grant proper permission to those tables. Then create a db user and put it in db_accessadmin and db_securityadmin role, so it can add other db users to proper role based on which table to access. |
 |
|
|
rocky1118
Starting Member
2 Posts |
Posted - 2007-09-03 : 01:49:38
|
| thanks rmiao. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 15:27:52
|
| slight modification... part of mastering Tai Chi (some singaporean joke i picked up working over there) -- the art of transferring responsibility...1. create domain groups2. add the domain group in sql server3. provide appropriate permissions to group4. let system admin add/remove members to the group depending on business policy such as termination or staff movementNo extra privilege to set for another 'admin', no cleanups with db security, the only shared responsibility with the sysadmin guys is giving select permissions on the tables specified in #3. No worries on password expiry since AD will handle that for you.Well I'm no guru but that's how I would do it if the members in question are IT staff.--------------------keeping it simple... |
|
|
|
|
|
|
creating user groups and users for schemas.