| Author |
Topic |
|
nicole
Yak Posting Veteran
97 Posts |
 Posted - 2006-03-01 : 03:14:21
|
| If the SQL Server has several database, can we apply access control to different user ID?For example:User A can access DB-1, DB-2, DB-3User B can access DB-2User C can access DB-2, DB-3 |
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-03-01 : 03:18:48
|
| In addition to the database access, is there any setting for user role? For example: User A can select/insert/update/delete against the tables records; User B can select only. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-01 : 03:18:50
|
| yes.see sp_grantdbaccess in BooksOnline----------------------------------'KH' |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-01 : 03:23:04
|
quote:
Originally posted by nicole
In addition to the database access, is there any setting for user role? For example: User A can select/insert/update/delete against the tables records; User B can select only.
Yes. you can control by individual user or use role To create a role use sp_addroleTo add a member to a role use sp_addrolememberFor more information, please refer to Books OnLine on Security----------------------------------'KH' |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-03-01 : 03:46:18
|
Thanks, KH  what if.....Developer A can access dev database, this can be done by using sp_grantdbaccess as you said. However, he also needs to develop DTS package, that means I have to grant him access to the "msdb" too? Since msdb is a system master table, I wonder if it should/have to be accessed by all users.... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-01 : 03:54:39
|
| I am not sure about this but i don't think you need to grant access of msdb to that user.----------------------------------'KH' |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-03-01 : 04:08:07
|
Since all DTS are kept under msdb, no matter for dev or production database, that means any DTS can be modified/delete by anybody who have access to the SQL server. Am I correct?My manager would like to have more control on database/table/DTS access, and I learnt from you that specific access can be granted to each database/table. However, I wonder if it's feasible on DTS  |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-03-02 : 23:03:36
|
| For the DTS, any access control I can apply? For example, set the user who can add/modify DTS only... |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-03-05 : 15:48:57
|
| nicole, take a look at this link. I think this answers all your questionshttp://support.microsoft.com/default.aspx?scid=kb;en-us;282463take a look at these points from the microsoft site. On SQL Server 7.0, users can be denied SELECT, INSERT, UPDATE or DELETE permissions on the msdb..Tfmpackage system table to prevent the user from viewing, creating, updating, or deleting packages that are stored in the SQL Server 7.0 version of the repository.• If users are denied execute permissions to the msdb..sp_enum_dtspackages stored procedure, the users cannot view any local packages. When the user clicks Local Packages in the SQL Server Enterprise Manager, the user receives an empty list.ThanksSree |
|
|
|
nicole
Yak Posting Veteran
97 Posts |
Posted - 2006-03-15 : 04:32:05
|
Thank you, Sree, the link you provided does help me alot  I think I can make use of the owner password of the DTS in addition to DB role to apply control on DTS  |
|
|
|
|
Database access