Application roles for multiple databases in SQL Server 2008 R2

Posted on 2011-03-14
Medium Priority
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.
Question by:Maha
  • 2
  • 2
LVL 25

Expert Comment

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

Expert Comment

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.
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 500 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!

Author Comment

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?
LVL 28

Accepted Solution

Ryan McCauley earned 500 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!

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

589 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