AS sp_grant_proxy_to_subsystem documentation error

The procedure sp_grant_proxy_to_subsystem is used to grant an added proxy to a SQL Server subsystem. This can of course also be done in the management studio GUI as well, but us hardcore T-SQL fans usually prefer to have a scripted version of all configurations. For source code reasons. And because we can. I set up the credential-proxy-subsystem-login configuration to test processing a cube from an account that should not have much access to anything else than processing, so I wanted to give the proxy access to the subsystem “Analysis Services Command”

— Create a new proxy and assign the credential to it EXEC msdb.dbo.sp_add_proxy @proxy_name=N’myProxy’,@credential_name=N’myCredential’,@enabled=1 — Grant subsystem access to the proxy EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’myProxy’, @subsystem_id=9 –subsystem id — Grant the login the permissions to use the proxy EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’myProxy’, @login_name=N’ADmyADaccount’ GO

But when I refreshed in management studio, SQL Server Agent, Proxies, “Analysis Services Command” I did not see the proxy. Rechecked the documentation in msdn and there I found an inconsistency in the value/description pairs described for subsystem_id and subsystem_name. Checked back in management studio, refreshing the next folder Analysis Services Query – and there it was. Either the documentation or the msdb procedures had got it wrong, leaning towards documentation since it already was an inconsistency there. And this is wrong for all versions from Denali back to 2005. Further analysis reveiled – in the dbo.sp_verify_subsystems I found these:

–ANALYSISQUERY subsystem INSERT syssubsystems VALUES ( 9, N’ANALYSISQUERY’, 14513, @InstRootPath + N’SQLOLAPSS.DLL’,NULL,N’OlapStart’,N’OlapQueryEvent’,N’OlapStop’,100 * @processor_count ) –ANALYSISCOMMAND subsystem INSERT syssubsystems VALUES ( 10, N’ANALYSISCOMMAND’, 14514, @InstRootPath + N’SQLOLAPSS.DLL’,NULL,N’OlapStart’,N’OlapCommandEvent’,N’OlapStop’,100 * @processor_count ) and when you lookup the system table for subsystems you get the answer, the proc is right (of course) and the documentation is wrong. 🙂 SELECT * FROM msdb.dbo.syssubsystems WHERE subsystem like ‘%ANALYS%’ —subsystem_id subsystem –9 ANALYSISQUERY –10 ANALYSISCOMMAND Of course I had to try other values as well and here is the results: (1 The Transact-SQL subsystem , cannot be executed under the context of a proxy account) 2  Microsoft ActiveX Script 3  Operating System (CmdExec) ( 4,5,6,7,8 – got assigned to “unassigned proxies”, but that is because I do not have replication activated on the instance, I guess. 4  Replication Snapshot Agent 5  Replication Log Reader Agent 6  Replication Distribution Agent 7  Replication Merge Agent 8  Replication Queue Reader Agent ) 9  Analysis Services Query ! 10 Analysis Services Command ! 11 SSIS package execution 12 PowerShell Script (new from 2008 version) The msdn listing of subsystem id: ================================== Value Description 2  Microsoft ActiveX Script (Important – The ActiveX Scripting subsystem will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. ) 3  Operating System (CmdExec) 4  Replication Snapshot Agent 5  Replication Log Reader Agent 6  Replication Distribution Agent 7  Replication Merge Agent 8  Replication Queue Reader Agent 9  Analysis Services Command 10 Analysis Services Query 11 SSIS package execution 12 PowerShell Script The msdn listing of subsystem name ================================== But in the subsystem name, if you start counting from number 2 (the ActiveXScripting) and count + 1 for each row in the listing, and following it down, the ANALYSISQUERY value is actually on the correct “ordinal position” = 9, and the next on the 10 = ANALYSISCOMMAND, but instead the description is incorrect. Value Description

ActiveScripting ActiveX Script CmdExec Operating System (CmdExec) Snapshot Replication Snapshot Agent LogReader Replication Log Reader Agent Distribution Replication Distribution Agent Merge Replication Merge Agent QueueReader Replication Queue Reader Agent ANALYSISQUERY Analysis Services Command ANALYSISCOMMAND Analysis Services Query Dts SSIS package execution PowerShell PowerShell Script Hopefully this is fixed to RTM, haven’t checked RC0 yet. Vote for it at the Microsoft Connect site if you want to help the feedback getting attention. Update 18:22: That was really fast. Posted this connect at about 13:00…They have already fixed it for 2008 R2 and 2012 RTM. Thank you Rick! “How silly to have them reversed. I have fixed that in the next release of SQL Server 2008 R2 and SQL Server 2012 (RTM).

Thank you for bringing this to my attention. Rick Byham”

/Jonas Bergström