2

How can i check, if an (and which) application role is active for a connection (DbConnection object) to the sql server?

My problem is, that all database objects have access granted for my application role, but when executing a select statement i get an "access denied" error. I use a single connection object with application role activated, but I fear, that either EntityFramework or the DI container creates another DbConnection object due to a configuration fault.

j0k
  • 22,600
  • 28
  • 79
  • 90
okrumnow
  • 2,346
  • 23
  • 39

1 Answers1

0

Doh. On re-reading the documentation for sp_setapprole, I see this sample code:

DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'
    , @fCreateCookie = true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- This will return the name of the application role, Sales11.

So it would appear that accessing USER_NAME() will do the trick.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • No chance to get the information w/o another statement execution? So that it can be seen in the debugger? – okrumnow Nov 17 '11 at 10:39
  • @okrumnow - Not so far as I'm aware, and I can't see a way to read it from another connection either (E.g. I was hoping it might show up in a DMV (e.g. `sys.dm_exec_sessions`, but I can't seem to find it anywhere) – Damien_The_Unbeliever Nov 17 '11 at 11:50