Well, it continues to be a pain in my ass, the inability to grant truncate table permissions on a table to specific users without giving them excessive rights.  SQL 2K5, no solution, SQL 2K8 no solution, well we did get a bunch of other stuff, so we'll continue to suffer.  I originally posted an article on Granting Truncate Table permissions in 2008, here is that link.  Recently the need has arisen to have the ability log failures for the solution.  The code was modified by Holland Humphrey and I'm posting the solution again with his updates.

The solution basically uses a schema, dba, to hold 3 tables for a stored procedure which is used to truncate tables.  The 3 tables, Truncate_List, Truncate_Audit and Truncate_Fail.  A user is granted permissions to a stored procedure in the dbo schema that performs the work and verifies that the table is authorized for truncate, and logs the success to truncate_audit and the failure to truncate_fail.  One could argue that truncate_audit could hold both success and failure, but I'll leave any possible enhancements up to those that desire to do so.

The original inspiration for this actually came from the Oracle group where I work.  Oracle has the same problem as sql "Grant Truncate on {table} to {User}"...so simple....but it's not there in SQL or Oracle.  I basically translated the Oracle solution into SQL Server.

Every upgrade we do from SQL 2000 to 2K5 or 2K8 uses this solution and allows us to reduce our permissions set to the lowest possible necessary.

Here are the scripts for the solution.

1.  Create a Schema Called DBA {if you prefer something else, adjust the tables and proc}.

2.  TruncateSolution_CreateTables.txt (4.67 KB)

3.  TruncateSolution_CreateProc.txt (4.34 KB)

I always thought the definition of getting screwed twice was this; A German student who auctioned her virginity and ended up paying 50% in taxes.

But it turns out that identity values in SQL Server can also screw you twice!

I used to really like identity values and problems have been rare, but this week, inside of a few days I've been screwed twice by identity values.  This is also the year of embracing merge replication, time to meet your new friend the guid!

No need to rehash what happenned with identity values, but altering a table from int to bigint with 30 billion+ rows is not possible.  Found two very helpful posts, i include their links here as they were most helpful:

To resolve (albeit temporarily) identity values running out for an int, do not forget you have the negative values.

http://sqlfool.com/2008/11/max-int-identity-value-reached-dbcc-checkident/
http://dbwhisperer.blogspot.com/2009/04/which-identity-column-is-running-out-of.html

Definetly we'll add a new automated health check, will probably fit in nicely with our script that check row counts occasionally to also start checking identity columns and raising alerts on certain thresholds.

I'll also be pushing back much harder on development teams that want identity columns with an int data type.