View Server State

• Koopa

  Old Hand

  Points: 368

  More actions

  March 25, 2014 at 2:07 pm

  #305644

  Okay, it's been a long day, I am going crazy.

  I have a database that has some stored procedures that need to view sys.dm_exec_connections.

  Stored procedures are execute AS OWNER. Owner is 'DOMAIN\ABC'.

  use master;

  go

  grant view server state to [domain\ABC]

  Success.

  execute as user= 'domain\abc'

  select * from sys.dm_exec_connections

  Msg 297, Level 16, State 1, Line 3

  The user does not have permission to perform this action.

  Sure enough, the stored procs don't work either. I must be missing something trivial at this point. I've double checked that the permissions are granted.

  :alien:

• SQLBlimp

  SSCertifiable

  Points: 6340

  More actions

  March 25, 2014 at 2:12 pm

  #1700053

  Koopa (3/25/2014)

  ---

  Okay, it's been a long day, I am going crazy.

  I have a database that has some stored procedures that need to view sys.dm_exec_connections.

  Stored procedures are execute AS OWNER. Owner is 'DOMAIN\ABC'.

  use master;

  go

  grant view server state to [domain\ABC]

  Success.

  execute as user= 'domain\abc'

  select * from sys.dm_exec_connections

  Msg 297, Level 16, State 1, Line 3

  The user does not have permission to perform this action.

  Sure enough, the stored procs don't work either. I must be missing something trivial at this point. I've double checked that the permissions are granted.

  :alien:

  I think that you need to execute as login and not as user. View server state is on the server and not on the database.

  http://msdn.microsoft.com/en-us/library/ms181362(v=sql.100).aspx

  Thanks

  John.