GRANT permission to an Application Role

Question

Hi,

I have a problem using GRANT.

I have 2 DB (A & B) on the same server.

On the A DB is defined an Application Role (MyApplicationRole).

How can I GRANT permission on DB B (or only on some tables) to MyApplicationRole ?

I didn't find the correct syntax...

Answer 1

Hi

I found a solution by myself.

Simply use the Impersonation (WITH EXECUTE AS ...) in the Stored Procedure that queries 2 DataBases.

Answer 2

Remember that his will either need a higher priviledged account than you want to OR you will need to enable Cross database ownership chaining, which opens a security hole as well.

Jens K. Suessmeyer

Answer 3

    An alternative to using Cross DB Ownership chaining (CDOC), application roles or EXECUTE AS in cross-database access could be using digital signatures to establish a controlled trust relationship. I wrote an article on this topic some time ago that I hope will be useful: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

BTW. Regarding cross-database behavior for application roles. Approles are not design for cross DB usage, the following thread describes in detail the restrictions around cross-DB access for application roles: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/b12821f5-fcd6-4d49-83b6-da7220339411/

 I hope this information helps; please let us know if you any question.

  -Raul Garcia
   SDE/T
   SQL Server Engine

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 4

Hi Jens, hi Raul,

I know that using Impersonation I open a security hole. Anyway my application is used only on a intranet, so I think I shouldn't have many problems.

Raul, thank you for your articles, I'll read them asap. I'm interested about digital signatures, I'll think about the possibility to use in replacement of Impersonation.

 Thank you very much !

Answer 5

Hi Nazza,

What sintax did you use to grant impersonate to your stored procedure?

Answer 6

Hi fgiacomel,
you can find the syntax in the MSDN, look the link below:
http://msdn.microsoft.com/en-us/library/ms188354.aspx

I used

WITH EXECUTE AS 'user_name'

where user_name has the permissions needed.