Solved

Application roles for multiple databases in SQL Server 2008 R2

Posted on 2011-03-14
5
1,190 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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

831 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