Application roles for multiple databases in SQL Server 2008 R2

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.
MahaAsked:
Who is Participating?
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
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
 
jogosCommented:
Are you talking aboout multiple databases or databases on multiple sql server instances?
0
 
jogosCommented:
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
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
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
 
MahaAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.