Looks like up until SQL Server 2000 there was an undocumented command DBCC CHECKDBTS
that did exactly what you want but it is no more. It looks like it is stored on the database boot page with no (safe, see below) way of manually setting it.
I'm not sure why you want to do this and I suggest that you don't bother but if you must then you will likely find set based inserts considerably quicker.
(NB: The below might need tweaking to avoid overshooting the target as I found that @@DBTS
incremented by 1003921
for each million row insert but should be an improvement...)
CREATE TABLE __DUMMY (I TINYINT, TS TIMESTAMP)
DECLARE @Target INT, @Batch INT
SET @Target = 666666666
WHILE @Target > CONVERT(INT, @@DBTS)
BEGIN
RAISERROR ('@@DBTS = %d',0,1,@@DBTS) WITH NOWAIT;
SET @Batch = @Target - CONVERT(INT, @@DBTS);
IF @Batch > 1000000
SET @Batch = 1000000;
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO __DUMMY (I)
SELECT 1
FROM cteTally
WHERE N <= @Batch;
TRUNCATE TABLE __DUMMY
END
DROP TABLE __DUMMY
Or alternatively if this is only a development database, you have backups and you aren't going to come complaining to me if it all goes wrong you could do some experimenting with the undocumented and highly warned against DBCC WRITEPAGE
command.
USE tempdb;
IF DB_ID('TestDBTS') IS NOT NULL
DROP DATABASE TestDBTS;
CREATE DATABASE TestDBTS;
USE TestDBTS;
DBCC WRITEPAGE(TestDBTS, 1, 9, 412, 8, 0xAA86BC2700000000);
ALTER DATABASE TestDBTS SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE TestDBTS SET ONLINE WITH ROLLBACK IMMEDIATE;
USE TestDBTS;
SELECT @@DBTS;
DBCC TRACEON (3604);
DBCC DBINFO ('TestDBTS');
DBCC TRACEOFF (3604);
Best Answer
This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via
EXECUTE AS
can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using theEXECUTE AS
. But with module signing: the Certificate-based User cannot be Impersonated (see final test case), another module cannot be signed without knowing the password for the Certificate, and if anyone changes any module that you sign (such as the Trigger), then the signature is automatically removed, alerting you to that change and then you can decide whether to resign it with the current changes or reject the changes ;-).Also, trapping ApplicationName / ProgramName in a Trigger is not reliable as it is easy to pass in that value in a ConnectionString.
Please note that the Audit table is in a different Schema --
Auditing
-- than the main table -- indbo
-- to prevent ownership-chaining, assuming that most Stored Procedures would also be in thedbo
Schema.The Setup
The Test
UPDATE
Additional notes:
db_owner
fixed database role should be able to disable the Trigger, and there is potentially at least 1 work around that could be used by someone in thedb_datawriter
fixed database role if they are fairly crafty.TRUSTWORTHY
.EXECUTE AS
changes the current security context. It is essentially saying: I am Login/User A, but for the moment, please use the permissions of Login/User B INSTEAD OF mine.** I do have mostly-complete example code (about 75% done) for a Trigger on the Audit table that would disallow updates from anything but code signed by the Certificate, but ran out of time to complete it. The concept is that a lock is taken on the Certificate during the process, and the lock entry includes the Certificate ID. You can verify that the Certificate ID is the desired Certificate and
ROLLBACK
if it isn't or no Certificate is used in the Transaction. The problem was thatVIEW SERVER STATE
is needed to usesys.dm_tran_locks
. However, that is a fairly easy problem to solve as it can be granted via a Certificate-based Login, which can even be the same Certificate. In that case, the Certificate can be backed-up and restored intomaster
for the purpose of creating the Login from it. Then just grant that Login theVIEW SERVER STATE
permission, and finally sign the Trigger on the Audit table with that same Certificate (already in that DB as it was used to sign the Trigger on the base table).