Cannot ALTER SEQUENCE in SQL Server

Question

I'm having trouble using the ALTER SEQUENCE statement in SQL Server.

This is the code I'm using:

ALTER SEQUENCE MakatiORSeries RESTART WITH 23;

And this is what I'm getting:

Msg 15151, Level 16, State 1, Line 7
Cannot alter the sequence 'MakatiORSeries', because it does not exist or you do not have permission.

But when I select all the sequences from sys.sequences, it is there

SELECT * 
FROM sys.sequences 
WHERE [name] = 'MakatiORSeries'

I tried these references too:

SQL Server sequence set current value

Use a variable or parameter to specify restart value for ALTER SEQUENCE

Any help is greatly appreciated. Thanks!

is the account you using have the permission? because you need certain level of permission to alter sequences. — T. Peter
– T. Peter, Commented Nov 18, 2020 at 4:23
I'm not really sure how to access those info in sql-server but try fn_my_permissions here is the msdn link : msdn — T. Peter
– T. Peter, Commented Nov 18, 2020 at 6:34
update: i just found out that if i try to add that ALTER code into an existing stored procedure in my project, it is working, but then when I call that stored proc in my solution, exception is thrown with that same error message — jansenzxc
– jansenzxc, Commented Nov 18, 2020 at 7:14

Randy in Marin · Accepted Answer · 2020-11-18 19:40:51Z

0

The ALTER permission on the sequence or the schema is required to change this. Ownership chaining in a procedure allows basic DML such as INSERT, UPDATE, DELETE, SELECT, and EXECUTE. It does not extend to ALTER. The account that runs the procedure will also need the ALTER permission on the sequence.

If there are permissions that can't be granted to the login because of security concerns, then you can sign the procedure to run as another credential.

answered Nov 18, 2020 at 19:40

Randy in Marin

1,1436 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jansenzxc Over a year ago

oh this makes why i cant run the solution, but in sql server itself it runs, that means the user im using in my solution has no permission right? Thanks!

jansenzxc Over a year ago

question, can I use update script instead of ALTER when setting the current value of sequence number?

Randy in Marin Over a year ago

You have to use ALTER. You are changing the object, not updating a record. Create a database role (e.g., AppRole) for the application and add the required user to this role. Grant the role the alter right on the sequence. The GUI can be used. Or perhaps GRANT ALTER ON OBJECT::dbo.MakatiORSeries TO [AppRole] is easier than the GUI.

jansenzxc Over a year ago

thank you very much! this helped me a lot as i was confused what to do

Jozott · Accepted Answer · 2021-01-05 14:48:59Z

-1

If you want to restart the sequence to set it to a specific value use

SELECT setval('seq',0,false);

It sets the value of the sequence "seq" to 0.

answered Jan 5, 2021 at 14:48

Jozott

2,7322 gold badges18 silver badges32 bronze badges

1 Comment

ModChowdhury May 10 at 15:34

does not work. Err msg ['setval' is not a recognized built-in function name.]

Collectives™ on Stack Overflow

Cannot ALTER SEQUENCE in SQL Server

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related