This week I set up a a user in our SQL Server. To save time, I simply assigned all of the permission that user needed. Then I remembered how many times I have been burned by this shortcut. It is so much easier in the long run to create a role in SQL Server, assign […]
SQL Permission Roles versus User Read More »
When using dynamic SQL there is always the possibility that someone will inject SQL that does harm. They can put queries that can drop objects or can create sysadmin logins. Running the scripts as low permission users can reduce the potential harm. But that takes time to research and is easy to cut. You can
Limiting permissions with “Execute as” when using dynamic SQL Read More »
One of the tasks that eats a lot of my ETL development time is documenting the views and procedures I write. I need to know where exactly the data is coming from. sp_describe_first_result_set provides a flag for outputting the source for many of the columns in a query. exec sys.sp_describe_first_result_set N’exec [Integration].[GetOrderUpdates] ”20100101”, ”20170122”’, null,
Getting metadata from query using sp_describe_first_result_set Read More »
In a previous post (Easing the Testing Stored Procedures with Temporary Tables) I introduced the sp_describe_first_result_set stored procedure. This procedure works like sp_executesql, except that it returns the columns that a query returns rather than the results of the query. But it has a problem. The column name is separated from the datatype and thus
Using a temporary table to handle sp_describe_first_result_set Read More »
Recently someone posed a puzzling question on the MSDN forums (How to reorder a column in a table which also has a foreign key). They needed to be able to add columns to a table in a specific order. If you know basics of relational theory, you would understand that the order of columns in
Using view as source for application Read More »
One of the query pains in SQL Server involves a simple split of a string. You want to pass into a stored procedure a comma delimited list of items and use that in your procedure. In order to get a tabular result from that string, you had to write your own function. But no longer.
Splitting strings in SQL Server Read More »
I think that we would all agree that we should test our code before we release it. Sometimes it very easy to do so. For example, testing a view is not difficult. Say you had to make a small change to the view. You create a new view that returns the same results that the
Easing the Testing Stored Procedures with Temporary Tables Read More »
One of the things that I do frequently is to put together some message that involves a template filled in with variables. For example, I want my error message to fit a certain pattern. Or I want to put a meaningful log message text. In SQL Server, I repeat the following ugliness in my code:
Displaying parameterized string in SQL Server Read More »