Ozzie.eu

Link: SQL Server Row Level Security | Official Pythian Blog

So I’ve read the article above and thought:

• Will managers realize they have to document a formal data security policy?
  
• Will DBA staff be accountable for wrong permissions: granting too much or too little access to data?
  

Overall, I think it’s definitely a good security enhancement but security lies on the weakest link, right?

The guys from SQLServerCentral.com published an article giving a Quick Tour of sp_ExecuteSQL. This is all very well, except for the antibodies in me regarding dynamic TSQL inside frequently used stored procedures.

Yes, it’s true that “Every once in a while you find yourself working with SQL Server and you need to execute a piece of T-SQL that will be  different at the time of execution from the time that the code is being written.” but pointing out SQL injection as the single concern is quite an understatement.

The key downside events regarding dynamic TSQL are statement compile time and execution plans. You can read on the official documentation at MSDN: “The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql.”

Furthermore “sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.”

One can try to optimize this dynamic execution by using parameter substitution, so the the Transact-SQL string is built only one time. But there are still aspects you can’t control:

• What is the generated execution plan for the dynamic statement, running separate from the execution plan of the batch that called sp_executesql;


• Is the execution plan any good? You can’t really tell to try to change it;


• Good or bad, the plan is cached for the following executions. If the diversity of queries that can dynamically be run is large, there’s no gain from the plan caching;


• Using the RECOMPILE option solves the above issue but creates another: each time the stored procedure is called, the execution plan is recompiled. For long running queries this might be overlooked, but for queries otherwise fast, it’s a major performance drawback.

Having stated my displeasure for the dynamic TSQL, I can also recommend the following readings:

• The Curse and Blessings of Dynamic SQL


• Is Dynamic TSQL a Good or Bad Idea?

I apologize for the rant but in all applications I witnessed the development, using dynamic TSQL inside stored procedures caused nothing but trouble as soon as the tiny tables turned into a larger, business critical database with hundreds of users.

While securing a Microsoft SQL Server instance, there are many issues you should look after. According to the official documentation, it can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. On environments where SQL Server is used on a more “Swiss Army Knife” fashion, there is often difficulty on disabling or, at least, restricting access to the general extended stored procedures, like xp_cmdshell. These stored procedures provide an interface from an instance of SQL Server to external programs for various maintenance activities. But they also represent a security liability. The alternative suggested is if possible to use SQL CLR Integration to perform these tasks. It can be done and it works, but maybe it isn’t a desired task by the regular DBA to learn to program a .NET Framework language.
With this in mind, as far as SQL Server Agent jobs are concerned, I’d suggest creating a Powershell job step:

Bear in mind this requires a specific kind of error handling. By default the ErrorActionPreference is set to Continue, and this has implications on how errors bubble up to the SQL Server Job Server. If you run a Windows PowerShell command as a SQL Server Agent job and there are no syntax errors but the command produces an error, the SQL Server Agent job will report success. If you want an error condition to halt execution of a SQL Server Agent job or to produce an error, you'll need to add some error handling. To bubble up Windows PowerShell errors to SQL Server Agent, you'll need to set your $ErrorActionPreference = "Stop". You can check that out here.
For the processing task which are a hybrid of data manipulation and other external system related tasks I’d suggest the development of Powershell scripts that use SQL Server Management Objects (SMO) which is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
Sure, you might say that this suggestion doesn’t help from learning to code, but this is an inevitable goal every SQL Server DBA must have as the future might have projects where the projects might have a server core platform. What then?
So why not kill two rabbits with one stone and learn your Powershell and SMO, it quite seems  the way to go. For starters, this might help.

By coincidence, as I'm writing this post, this editorial from SQLServer Central came out. What are the odds of that?

Happy coding!


Photo Credit: baboon™ via Compfight cc

Microsoft SQL Server comes with a great tool for database administrators and developers alike, called Management Studio. SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SQL Server Management Studio combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.
But like most database management tools, it lacks a more automated support for some recurrent tasks that should be easier to perform.A developer will certainly wish for automatic generation of CRUD statements for a given database table. And a database administrator would surely like to run a maintenance script on all databases where it should be executed, all servers at once.
If these kind of enhancements are on your wishlist, be sure to check out the SSMS Tool pack. It’s a free add-in for SQL Server Management Studio with a great list of enhancements:

• Execution Plan Analyzer
• SQL Snippets
• Window Connection Coloring
• Tab Sessions, Window Content History, Query Execution History and Current Window History
• Format SQL
• Search Table, View or Database Data
• Run one script on multiple databases
• Copy execution plan bitmaps to clipboard or file
• Search Results in Grid Mode
• Generate Insert statements from resultsets, tables or databases
• Regions and Debug sections
• Running custom scripts from Object Explorer
• CRUD (Create, Read, Update, Delete) stored procedure generation
• New query template

You can download it from here.

Using Microsoft SQL Server, found this to be handy sometimes:

USE [your database name]
GO

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%given column name%'
ORDER BY schema_name, table_name;

HTH.