user can see all in sysprocess without VIEW SERVER STATE being granted

• billburke

  Say Hey Kid

  Points: 704

  More actions

  June 28, 2011 at 5:40 pm

  #148249

  Hello,

  We run SQL 2005 (9.0.4207) on several hundred servers that are distributed around the country. Each CPU hosts a SQL Server db for that location's OLTP database. The several locations do NOT communicate with each other.

  Our proprietary application runs under a SQL login that I’ll call “A”. That application sometimes queries master..sysprocess.. We have NOT granted server permissions for VIEW SERVER STATE to “A” on ANY of the production servers. “A” has db_owner privileges on our production databases but is NOT a sysadmin on the server. However, in many of the locations, “A” still sees ALL processes running on the server. This is the behavior that we desire.

  I've queried sys.server_permissions in all production locations, “A” does not have VIEW SERVER STATE permissions. HOWEVER, in SMMS\Object Explorer; right click on the server; choose “properties”; in the “Select a page” section choose “Permissions”; choose “A” in the “Logins or roles” section; clicking on Effective Permissions INCLUDES VIEW SERVER STATE in the locations where “A” sees all sysprocesses activity. The same process does NOT include VIEW SERVER STATE for “A” in the locations where “A” cannot see all sysprocesses activity.

  I've also queried sys.certificates in all production locations, and zero data is returned, so it is not accomplished with a certificate.

  We do not want to grant VIEW SERVER STATE permissions to “A”.

  How were the Effective Permissions for “A” elevated to include VIEW SERVER STATE without an entry in sys.server_permissions? How do I allow “A” to see all the results of a query to sysprocesses without granting VIEW SERVER STATE access on the production servers?

  I’ve spent several hours on the internet trying to find the answer to this. I’ve spent considerable time trying to give you all pertinent information in a succinct form, but please let me know if I’ve left any important information out. Any ideas would be appreciated.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 28, 2011 at 5:56 pm

  #1345588

  I would guess that login 'A' has the processadmin server role - that does not show up in server_permissions.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]