Application roles for multiple databases in SQL Server 2008 R2

Posted on 2011-03-14
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:navindba
  • 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 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!

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 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!

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now