June 28, 2011 at 5:40 pm
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.
June 28, 2011 at 5:56 pm
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);
June 28, 2011 at 6:08 pm
Mr. Magoo,
You seem to have PERFECT eyesight to me!!! You're guess was absolutely correct. I am embarrassed that it was this easy.
Thank you, Sir!!
June 28, 2011 at 6:13 pm
No, don't be embarrassed, I quite often rely on talking to others to spark something - face to face it often happens before you finish the sentence, online it can take longer.
Glad to help.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply