SQL Server - User Could Not Execute Stored Procedure sp_enable_sql_debug

Question:  When running SQL Debugging I get this SQL error. How can I fix?

 

 

The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

 

 

Answer: SQL Debugging requires sysadmin permission. This is unfortunate for DBAs , as they are forced to give developers sysadmin privileges, which usually  is against most SQL Server Security Policies.

As a rule I don’t give developers Sysadmin rights . There normally has to be a strong case for sysadmin rights

As a note from Microsoft

“We recommend that you debug Transact-SQL code 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 has ended or the transaction is committed or rolled back.”

Related Posts

SQL Server – How to troubleshoot query timeouts

SQL Server - ALTER TRACE and developers

SQL Server Security Policy

SQL Stored Procedure Commenting

Question : Do you have a template for commenting in a  SQL Stored Procedure?

Answer: If you maintain a  source control system  ,  you probably maintain detailed comments,  and version history.

I work with a  DBA  who argues , comments in source control systems are sufficient. I disagree.  Maintaining comments on code deployed has many benefits. Such as :

 a)       Troubleshooting b) documentation .

There are some other  useful techniques available to for commenting  on database objects

SQL Server has a feature called Extended Properties  which supports adding meta data information to objects .

I distribute this example to developers. Maintaining comments with code is part of the Production approval process - and part of my Stored Procedure Checklist

----------------------------------------------------------------------------------------------
-- OBJECT NAME         : MyStoredProcedure
-- INPUTS    : @myParam1 NVARCHAR(128),@myParam2 INT
-- OUTPUTS    : @myOutParam1 NVARCHAR(128)
-- DEPENDENCIES         : None
-- AUTHOR    :     Jack Vamvas
-- DESCRIPTION         : This stored procedure accepts 2 parameters 
--
-- EXAMPLES (optional)  : EXEC MyStoredProcedure ‘sqlserver’, 2
--
-- Version HISTORY : version|Author|Date| Comment
---1.0      JV 7\10\2011 Initial Version
--1.2       JV 11\10\2011  added CONVERT

SQL Server - Stored Procedure Checklist

DBAs find it easier to manage large Database Server environments when checklists are used. This Stored procedure checklist  will stimulate some ideas you could use

For acceptance into the SQL Server environment , I’ve developed some scripts that scan the T-SQL code for some items in this checklist

1)  Same case for T-SQL Keywords. I prefer UPPER CASE , but I know other DBA\Developers like lower case.

2)  Inspect input parameters for valid values (numbers, strings etc)

3)  Force developers to justify  usage of  dynamic SQL (SP_EXECUTESQL, EXEC('SELECT col1…..')

4)  Commenting. I prefer a comment block at the top with usage and revision history . Snippets throughout the code to enhance the understanding

5)  Limit xp_cmdshell usage

6)  Think in set theory. Avoid looping  such as cursors.

7)  Clean up . Close objects ,drop #temp tables, deallocate\close cursors, close transactions

8)  Use try...catch blocks. Trap errors and report into a log file

9)  Performance is satisfactory

10) Investigate input parameters for SQL injection attacks. Better if it occurs at the application level .

 11)  Manage  security. Follow the SQL Server Security Policy.Access to stored procedure and underlying objects

Read More

SQL Server - Stored Procedures versus ad-hoc paramaterized queries.Which is faster?

Last time a stored procedure was executed - SQL Server DBA

Grant execute on all stored procedures - SQL Server DBA

Search for text in stored procedures - 2000 - 2005 - 2008 - 2012

Search for text in stored procedures is useful, a typical example is object renaming\remapping.

There is one example for SQL Server 2000 and two examples for SQL Server 2005 - 2008 - 2012

USE MyDatabase
GO
-----SQL Server 2000
SELECT DISTINCT so.name ,so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%myText%'
ORDER BY so.name


-----SQL Server 2005 - 2008 - 2012
GO

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition LIKE '%some_text%'
AND o.type='P'

 Related Posts

Stored Procedure Checklist

SQL Stored Procedure Commenting