Xp_cmdshell – the most dangerous extended stored procedure on the planet

Xp_cmdshell is possibly the most dangerous extended stored procedure on the planet. I’ve seen some serious impact on a combination of xp_cmshell enabled and sysadmin rights. The main reason for it’s dangerous image is xp_cmdshell allows shell scripts to execute on OS. My general rule is to not allow non DBAs xp_cmdshell direct access. If a user requests xp_cmdshell with a valid and pragmatic reason, then I’ll set up a stored procedure – allowing them to run only xp_cmdshell through this stored procedures. Think about adding a check on input parameters to this managed store procedure There are valid reasons... Read more →

Bad indentation in stored procedures

Bad indentation in a stored procedure is inefficient. I receive daily requests to analyse sql queries for performance issues , but quite often I spend the first part of the job applying indentation to make the code easier to read. I don’t think bad indentation as bad coding and it doesn’t make the code run inefficiently. But it does make troubleshooting a slow running query more difficult. In an earlier post - Stored Procedure Checklist - I highlight a checklist of items which have positive performance impact on the query. MAKE SQL QUERIES EASIER TO TROUBLESHOOT When I start analysing... Read more →

How to list all CLR objects and associated procedures

A developer reported an error in the Microsoft .Net Framework when executing a CLR object. Use the query and CLR views to understand and troubleshoot CLR integration managed database objects that are executing in the SQL Server instance. An error occurred in the Microsoft .NET Framework while trying to load assembly id xxxxx System.IO.FileLoadException: Could not load file or assembly 'myapp.database.sqlserver, Version=2.1.40317.0, Culture=neutral, PublicKeyToken=xxxxxxxx' or one of its dependencies. An error relating to security occurred. How to troubleshoot .Net Framework errors and CLR objects These type of errors can be difficult to troubleshoot. One of the reasons they can be... Read more →

WITH RECOMPILE option and stored procedure

Using the WITH RECOMPILE option in a stored procedure generates a new execution plan each time the query is executed. This technique is often used for queries that require high performance in certain situations, such as using CASE block where different columns may be used for a search. But , with the flexibility WITH RECOMPILE offers , it comes with a cost of increased CPU usage For a simple query , there may be minimal overhead, but for complex queries – some serious CPU time may be required to create a query plan. As the developer , you’ve requested what... Read more →

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... Read more →