security context for dbcc

Question

security context for dbcc

win

SSCommitted

Points: 1569
More actions
January 12, 2005 at 1:25 am

#91919

The procedure below is owned by db_owner and database users have permission to execute this procedure. I was hoping that dbcc ... would run in ther security context of db_owner, but it does not. Is there a way to make it work. If database users delete many rows, a trigger would execute the procedure to re-seed the Identity PK:
create procedure QSP_RESEED @seqtab varchar(50),@tartab varchar(50),@gid varchar(50)
as
declare @seedval int
declare @currid int
declare @offset int
declare @stmt nvarchar(500)
-- declare @usr varchar (50)
-- select @usr=user
-- exec sp_addrolemember 'db_ddladmin',@usr
set @stmt='select @offset=IDENTITYOFFSET from IDENTITYRANGE where TABLENAME='''+@tartab+''''
exec sp_executesql @stmt,N'@offset int output',@offset=@offset output
begin tran
    set @stmt='select @currid=max('+@gid+') from '+@tartab+' with (TABLOCK,HOLDLOCK)'
    exec sp_executesql @stmt,N'@currid int output',@currid=@currid output
    set @seedval=@currid-@offset
    dbcc checkident(@seqtab,RESEED,@seedval)
commit tran
-- exec sp_droprolemember 'db_ddladmin',@usr
GO
Cheers,
Win

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

magasvs SSCertifiable Points: 7660 More actions · Answer 1

May be it would be better to run scheduled SQL Agent job with this procedure. And it is also better to run this job on nights as soon as you use TABLOCK.

win SSCommitted Points: 1569 More actions · Answer 2

The tablock lasts for < 1 sec on a 60 million row table, so not a problem for a warehouse database. If you leave the re-seed job to a mignight agent you potentially will freeze large gaps into the available Identity range.

Cheers,

Win

M Chabot SSCrazy Points: 2511 More actions · Answer 3

Did you happen to find the solution to this problem? I am trying to run this code as the DBO and I get permission denied errors on the DBCC. If I run the code under the SA account, it works. The manual says that CHECKIDENT can be run by the DBO, but it is not working.

CREATE TABLE dbo.#insert (fldRecNum INT IDENTITY(0,1) PRIMARY KEY)

DBCC CHECKIDENT ('#insert', RESEED, @RecNum)

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 4

A temporary table gets created in TempDB. Therefore, the CHECKIDENT would have to run against a table there. Is the login in question a dbo over TempDB?

K. Brian Kelley
@kbriankelley

win SSCommitted Points: 1569 More actions · Answer 5

M Chabot

No, I have not found the solution I was looking for. Instead, I start the Identity with -2147483648. I still can move to bigint if needed.

Win

M Chabot SSCrazy Points: 2511 More actions · Answer 6

Thank you. That makes sense to me. No, that user was not a dbo of TempDB.