T-SQL: Allow low privileged user to Truncate table

The problem with TRUNCATE is that it is a DDL statement rather than the regulation DELETE statement which is DML. As such, its not captured by using the db_datewriter database role and so on the face of it, you're left with two options:

1) Add the user to db_ddladmin role (or db_owner)
2) Grant ALTER table permissions to the table(s) in question

I'm not really a fan of either of these approaches as the first gives far too much control to the user and I try to avoid giving object level permissions if possible.

An alternative is to make use of the EXECUTE AS clause and wrap your TRUNCATE statement within a stored procedure and grant permissions on that instead.

-- create a test database 
CREATE DATABASE TruncateTest
GO
USE TruncateTest
GO
-- create a low privilege user to test with
CREATE USER NoRights WITHOUT LOGIN
GO
-- create a table to test against
CREATE TABLE dbo.TestData (Id INT IDENTITY(1,1))
GO
-- add some dummy data
INSERT INTO dbo.TestData DEFAULT VALUES
GO 20
-- check the data is ok
SELECT COUNT(*) FROM dbo.TestData
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- try to issue the truncate command
TRUNCATE TABLE dbo.TestData
GO
Msg 1088, LEVEL 16, State 7, Line 1
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO
-- create a wrapper procedure to truncate the table
CREATE PROCEDURE dbo.TruncateTestData
AS
TRUNCATE TABLE dbo.TestData
GO
-- grant execute to the test user
GRANT EXECUTE ON dbo.TruncateTestData TO NoRights
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO
Msg 1088, Level 16, State 7, Procedure TruncateTestData, Line 4
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO
-- modify the procedure to execute in the context of the owner (dbo)
ALTER PROCEDURE dbo.TruncateTestData
WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE dbo.TestData
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO
-- switch back to the superuser
REVERT
GO
-- check the data
SELECT COUNT(*) FROM dbo.TestData
GO
-- tidy up
DROP DATABASE TruncateTest
GO

SSRS: Use Stored Procedures in Datasets

This is my contribution to T-SQL Tuesday #24 hosted by Brad Schulz (blog) on the subject of Prox ‘n’ Funx (Stored Procedures and Functions to you and me :-) ).

I'm a big believer in using Stored Procedures (or at the very least, UDFs) for your Reporting Services datasets. and separating your presentation layer from your data layer and moving the SQL code away from the RDL.

The benefits of this are that you as long as the meta-data of your Stored Procedure stays the same, then you able to modify and enhance your SQL code without having to touch the RDL. You essentially abstract away the source code from the report.

Perhaps you're improving performance by moving to JOINs from cursors, extended the business logic to only return rows that meet new criteria or simply doing a refactoring of SQL code to standardise your table names. All of these don't affect the presentation layer and having them reside as Stored Procedures on the database, gives huge maintenance benefits.

Other advantages include having all your T-SQL held in the one place and knowing that you are aware of the impact of any changes without having to worry about dependancies elsewhere. Also, you will often be re-using code (eg for parameter datasets) and using a single stored procedure helps reduce duplication of effort (and probably performance benefits too).

Of course, there are downsides to this approach. If you need to introduce a new parameter to a report (which is passed to your dataset) then you have to change both the RDL and the stored procedure. I can see this being a slight irritation as you now have 2 deployments whereas holding the SQL code "inline" means a simple upload of the new report.

For me though, the former approach still wins and I advocate using Stored Procedures for Reporting Services datasets. I've been experimenting recently with putting Stored Procedures used for Reports into their own schema (acting as a namespace) although I can't categorically say whether this has been a success or not (Jamie Thomson (Blog | Twitter) has an interesting blog post which touches on Schema usage here).

T-SQL: Beware the hardcoded switch

This months T-SQL Tuesday is brought to us by Adam Machanic (Blog|Twitter) - and as is his right, has chosen to go for Wednesday rather than Tuesday just to keep everyone on their toes. The subject is around c**p code, something which we're all guilty of in some form of another either as a result of laziness or lack of knowledge.

My contribution is fairly short and sweet today and something I'm often guilty of: The hardcoded lookup value embedded within a Stored Procedure or Function to control logic. You'll have seen this before (certainly if you've ever worked with me!)

IF @TheIDOfThisPerson = 15
EXEC dbo.UseThisStoredProcedure
ELSE
EXEC dbo.UseThisOtherOne

I often do this as a "temporary" hack to test some code or demonstrate a proof of concept, but it shouldn't come as a surprise to me when this finds its way into production. Fortunately, its been rare when the issue has caught me out in any serious way but I often find myself fixing stuff in a test environment after promoting code through the Development tiers because the ID values don't match across all servers. There is almost always a better way to achieve this and its often only an extra few clicks on the keyboard but somehow it still creeps into my game and other developers I work with.

I don't think anyone consciously CHOOSES to write something poorly but as we're constantly learning, its an inevitable byproduct. But lets embrace it. Just as long as we don't beat ourselves (or each other) about it, we can use it in a positive way as a learning point. While a developers goal is often to write less code, I prefer to focus on writing less C**P code and I don't think you have to sacrifice one over the other.

SSMS: Beware of Modify

When you want to change the defintion of a sql programmability object in SQL2008 eg Trigger, Stored Procedure or Function using SQL Server Management Studio, you get to options when right clicking the object, Modify or Script..ALTER to.



I've never really understood the differences between the two as both appear to have the same behaviour of scripting out your object with an ALTER command. But there does appear to be a minor difference, one which i'm sure isn't by design, is only minor but definitely one to be careful of.

When using Modify to script out your object to a new window the GO separator is not included whereas it is included using the Script command. So if you happen to make a few modifications in the same window below the end of the definition of the object to test something "ad hoc", you'll need to remember to highlight only the part of the query window which forms the object. Otherwise, your testing statements will be included as part of object when you just hit F5 to persist the ALTER.

In the following screenshot, i've hit the Modify option and then written an adhoc query for a different purpose. If, I forget to remove the adhoc statement, forget to add a GO statement at the end of the procedure or don't highlight the procedure definition, this test query will be persisted as part of the procedure leading to some undesirable results.