none
Debug Stored Procedure without SysAdmin account RRS feed

  • Question

  • Hello,

         Is there a way to debug stored procedure in SQL 2008R2 from SQL Query Analyzer (Management Studio & Visual Studio) without needing to have SysAdmin account?  Obviously from access control standpoint in an enterprise like ours, providing SysAdmin privileges to a non-DBA has met with lot of resistance.

    Thanks,

    Gaja Kannan


    • Edited by GajaKannan Friday, March 9, 2012 7:58 PM
    Friday, March 9, 2012 7:52 PM

Answers

  •  >>Why should I be a SysAdmin. Just a schema owner should be enough correct?

    Taken from http://msdn.microsoft.com/en-us/library/s0fk6z6e.aspx

    We recommend that Transact-SQL code be debugged on a test server, not a production server, for the following reasons: Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server. Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session is ended or the transaction committed or rolled back.

    If you have concerns you may want to report your wish at microsoft connect site

    http://connect.microsoft.com/sqlserver




    • Edited by Chirag Shah Friday, March 9, 2012 8:23 PM
    • Proposed as answer by Stephanie Lv Monday, March 12, 2012 1:14 AM
    • Marked as answer by Stephanie Lv Monday, March 19, 2012 8:25 AM
    Friday, March 9, 2012 8:21 PM

All replies

  • Debugging is a highly privileged operation. That is why only members of the sysadmin fixed server role are allowed to debug in SQL Server.


    You may want to use a Test Server for debugging where you can be granted sysadmin permission.


    Friday, March 9, 2012 8:05 PM
  • Chirag, Absolutely agree with your statement. I am not talking abour Prod environment. We dont even have ID in prod environment. I am talking about test and dev servers. I see DBAs point that we should not be SysAdmins, but that is the only way we can debug in non-prod environments. Why should I be a SysAdmin. Just a schema owner should be enough correct?


    Gaja Kannan

    Friday, March 9, 2012 8:11 PM
  •  >>Why should I be a SysAdmin. Just a schema owner should be enough correct?

    Taken from http://msdn.microsoft.com/en-us/library/s0fk6z6e.aspx

    We recommend that Transact-SQL code be debugged on a test server, not a production server, for the following reasons: Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server. Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session is ended or the transaction committed or rolled back.

    If you have concerns you may want to report your wish at microsoft connect site

    http://connect.microsoft.com/sqlserver




    • Edited by Chirag Shah Friday, March 9, 2012 8:23 PM
    • Proposed as answer by Stephanie Lv Monday, March 12, 2012 1:14 AM
    • Marked as answer by Stephanie Lv Monday, March 19, 2012 8:25 AM
    Friday, March 9, 2012 8:21 PM
  • Truly unhelpful to just repeat what you already posted, without regard to the actual question being asked.

    We need a little more involvement than copy/paste from MS support. Sorry YOU may have been frustrated by the question. Think about how much your lack of real attention frustrates us, eh?

    Tuesday, October 23, 2018 7:23 PM
  • correct. "debugging is a highly privileged operation. Therefore.. bla..bla.." . lame excuse from MS not being able to develop a software that can address the challenges from real world scenarios. shame on you
    Thursday, November 14, 2019 10:25 PM