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 |
|
cyorka
Starting Member
7 Posts |
 Posted - 2003-05-05 : 10:11:47
|
| I am wondering if anyone could address a specific challenge I have:I need to create a set of logins, users, and/or roles that will accommodate three levels of database access across three different servers. Going from highest to lowest, the levels are as follows:Level 3: Access to all databases (multiple servers)Level 2: Access to different subsets of databases (could be multiple servers)Level 1: Access to one database (one server)We currently are using SQL Server authentication with a unique login for each database (we’ll eventually move to Windows authentication). A user connects to SQL Server through a front-end application using an existing database login and will have only one level of access.If possible we’d like to keep the existing logins (one per database), but now allow this multi-level access. I’m thinking a second connection will have to be made with a different login allowing the specific level of access?Any input would be appreciated. |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-05-05 : 10:21:45
|
| Have you looked into Application Roles? You assign the different access levels to three different app roles. When a user connects, you determine which set of rights they should have and then execute the appropriate app role. When the app role is executed, the connections current set of permissions are dropped and the connection is assigned the rights of the app role. Check BOL for more details.Jeff Banschbach, MCDBA |
 |
|
|
cyorka
Starting Member
7 Posts |
Posted - 2003-05-07 : 12:28:17
|
| Thanks for the feedback. Unfortunately, I don't think this will work for me since I need the logins to access multiple databases. If I'm understanding correctly, the application role applies to one database, and the "guest" user has to be in the other databases for the role to gain access. As a result, the application role cannot distinguish between what databases it can access and which ones it cannot, since all databases will have the guest user. |
|
|
|
|
|
|
|
|
Multi-level access