Solved

Application roles for multiple databases in SQL Server 2008 R2

Posted on 2011-03-14
5
1,285 Views
Last Modified: 2012-08-14
We want to use application roles in MS SQL2008R2 to access multiple databases through an application. Microsoft solution is we assign appropriate permission to 'guest' database role in order to access more than one database. Are there any other methods of accessing multiple databases through a single application using the application roles?

We would not like to use 'guest' because the application role will have dbo access and I suppose, in order to access multiple DBs via application role, we will have to give dbo access to ‘guest’ user on DBs which is a no no.
0
Comment
Question by:navindba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 35137160
Are you talking aboout multiple databases or databases on multiple sql server instances?
0
 
LVL 25

Expert Comment

by:jogos
ID: 35137405
Depending on what access is needed you can concider to make the access as a part off that one database.  View or procedure to acces tables in other database.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 125 total points
ID: 35138929
Application Roles are database-specific - when an account assumes an application role in a particular database, that connection doesn't have any access to other databases aside from the Guest access it normally does - that's why it was recommended that you accomplish what you want by granting rights to Guest. I agree with you, though - that's a bad idea on so many levels and I can't believe it was recommended as a solution.

Unfortunately, the only way to accomplish this is either to grant rights to everybody's login to do what's needed in the application, or to have shared logins used by the actual database connects, and move the authentication to the database layer through some kind of lookup table. Not pretty, but there's no way I know of to grant cross-database permissions to a role - you can't create new system roles and application roles are confined to a single database.

One (moderately kludgy) option you have is to have an application database that contains a collection of stored procedures that fetch all the data for you from the other databases, and then grant EXECUTE on these stored procedures to your application roles. The stored procedures encapsulate the permissions needed, since they run as their creator, and if you contain them all in a single database, then you can use role-based security to control access to them and then add other database users to those roles instead of granting rights directly to them. A bit of a workaround, but might give you what you need.

Good luck!
0
 

Author Comment

by:navindba
ID: 35139129
Thanks for the reply.
The multiple databases are on the same instance, each database has almost couple of hundreds of views and couple of hundreds of SPs for the local database that the application uses, like this application uses 8 databases, I believe if we have to change all these views and SPs to bring them in a single database that will require quite a bit of work.
I have a thought of creating an application role in each database with dbo access permission instead of giving the application role access to other databases via 'guest' user, but not sure if application roles are also meant to work like that, any suggestion on this? If I am wrong is there another way please?
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 125 total points
ID: 35139345
Don't grant DBO rights to your roles or users, however you create them - please! That will allow anybody with a login to your application to create objects, drop objects, and read/modify sensitive data, and defeats the purpose of security at all - if you're going to do that, you may as well go the Guest route that was previously recommended to you.

You may have to go the route of a role in each database, and then your application will have to handle which database to connect to and while role to assume when it connects. You won't be able to do cross-database queries, since your roles are single-database-only, but you can use Stored Procedures where it's necessary to accomplish this.

A bit of a pickle, and it's disappointing that role-based permissions are limited in this way, but that's the way it is, I suppose. Good luck!
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question