Some way around this?

[Aspnot]

I have a stored procedure that is called by users with DataReader and DataWriter rights. This stored procedure is used in the monthly closing for this company. It removes all of the records from the PaidOuts table and then needs to reset the Identity Seed to 0.

When we went live I used:

DBCC CHECKIDENT ('PaidOuts', RESEED , 0)

However, that gave an error stating that the user did not have enough permissions on the table.

I then changed the code to:

TRUNCATE TABLE PaidOuts

This, too, gave me the same message. What permissions would be needed to execute one of these statements?

Is there any way to have a DataReader/DataWriter user reset the Identity seed for a table?

[Asgorath]

run them as sa

[szlamany]

sa is sql authentication - you should not even be using sql authentication...

At any rate - what PERMISSIONS have you GRANTED to the SPROC that does this?

[Aspnot]

I have granted Domain Users (this is a very small office) DataReader and DataWriter rights to the applications. And that group has Execute permissions on the SP.

[szlamany]

Books Online shows the permissions needed for CheckIdent

Permissions
DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role, and are not transferable.

Seems to be the same for TRUNCATE - interesting...

Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

[Aspnot]

Any other thoughts on resetting the Identity for this table? This is a table that holds information about Petty Cash pay outs. There really aren't that many during each month. They just like to see the reference number incremented starting at 1.

I could create another column in the table that is just a Count() + 1.

[szlamany]

Are you against giving one of those permissions to the users who might run this option? Like the DB_DDLADMIN role?

[Aspnot]

Well... I currently just use the Domain Users account. I could just assign Domain Users to the DB_DDLADMIN role. I don't see much harm in that.