Permissions and sp_sdidebug

Any SQL Server user account involved during SQL debugging must have "exe- cute" rights on an extended stored procedure called sp_sdidebug , a right that only the system administrator account (sa) has by default. This extended stored procedure seems to be an application programming interface (API) to the previously mentioned mssdi98.dll, though I can't find any publicly available documentation for it. The sdi in the stored procedure's name stands for SQL debugging interface .

Whether the SQL Server account belongs to a developer who is debugging via Visual Studio's Server Explorer window or using Query Analyzer, or the account is the one specified in the SQL Server connection string used by an application, you need to establish that this account is permitted to execute sp_sdidebug . To check this, use the account to log onto the SQL Server and then type the following SQL command using Query Analyzer or any other tool of your choice:

 EXEC master..sp_sdidebug 

You'll see either a result stating that the command completed successfully or an execute permission error. If you see the latter result, you should also check that the account has permission to the master database itself. It's not unknown for a DBA to give permission to the stored procedure, but not to the master database. The quickest way to grant execution rights for a SQL Server account to sp_sdidebug is to enter the following SQL using a tool such as Query Analyzer:

 GRANT EXECUTE ON sp_sdidebug TO SpecificAccountName 

Another permission issue with sp_sdidebug can occur with SQL Server running under Windows NT 4.0. Some NT 4.0 configurations have the SQL Server service set to run using the System account, which doesn't have permission to execute the sp_sdidebug stored procedure. To make this work, you can change the user that runs the SQL Server service. You can do this by going to the service configuration screen, clicking the Start button, and selecting Settings ’ Control Panel ’ Services ’ MSSQLServer ’ Properties. Then go to the Startup section and change the Logon account from System to a specified user account that you know is allowed to execute the aforementioned stored procedure. Once again, this workaround is only necessary if you have SQL Server running under Windows NT 4.0.

Finally, if you've installed SQL Server SP3, then you'll encounter a new security switch that was added as a part of SP3. This switch only affects SQL debugging from a client application, not SQL debugging directly from Visual Studio. To gain permission for SQL debugging with SP3, you have to execute the following additional command:

 EXEC master..sp_sdidebug 'legacy_on' 

Unfortunately, this setting isn't persistent, so you'll need to re-execute the command if you reboot your SQL Server.