Is there any method to reset the Identity value other than the DBCC CHECKIDENT command
-
Hi,
I have a requirement to reset the identity column of a table , but the user doing this will not be having much access rights. I have read some where that the DBCC commands requires the user to have some access rights.
Is there any method to reset the Identity value other than the DBCC CHECKIDENT command.
Thanks in advance,
Rajesh
-
Keep in mind identities can have gaps. Your user may not want this
You could have your user raise an alert which launches a job , owned by an account wich has the needed authority, and have that job execute the checkident.
HowTo ? Check http://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=1032
The job might be something like this :
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'CheckIdent_For_MyUser', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'CheckIdent yourtable', @command = N'DBCC CHECKIDENT (yourtable)', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT
TRANSACTION
GOTO
EndSave
QuitWithRollback
:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave
:
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The rights required are ddladmin, db_owner or sysadmin. If you are fine with granting ddladmin you will be fine. Other method is to use TRUNCATE TABLE (resets to 0) but has the same requirements than check ident.
The job proposed by ALZDBA can also be pooling a "request table" and when the user enqueues a request the pooling job will execute it.
Your Call!
* Noel -
What is that requirement? Why does this need to be done?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Perhaps if you place the DBCC within a stored procedure and give the user execute permissions on the sprock?
-
Easiest way to do this (if you need to, answer above questions), is to create a table (tablename, idreset). Have the user insert a value into this table, say the name of the table and a 1 for resetting identity. Maybe you need another col to determine what the reset is.
Now have a job, running in DBO or sysadmin context, that reads the table and when it sees a row, it resets the identity.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply