Table has identity value with primary key as big integer.

Stored procedure is running Return Scope_Identity() which works until the identity values exceeds the value of the implicit integer conversion, 2,147,483,646.

This is documented, but rarely run into.

The correct best practice is too use an output parameter for the stored procedure.

This requires changing the procedure and calling code, which can be difficult if this happens in production environment during a busy period.

The temporary solution is too reset the identity value to a negative values, as the range of an integer, -2,147,483,646 to +2,147,483,646.

Watch when you do this, as checkident('tablename') will reset an identity value, so be sure to use noreseed as part of the command.

For monitoring purposes:

 

and some discussion on the work-around, which is too return the value as an output parameter.

 

https://stackoverflow.com/questions/25915653/return-bigint-in-a-stored-procedure

Granting Create Table permissions to a specific schema requires, granting create table at the database level and alter at the schema level (doing no grants to the dbo schema).

Create user [some user] from external provider

go

Grant alter,select,insert,update,delete on schema::[schema name] to user [some user]

go

-- if control or references is needed

-- grant control,references on schema:: [schema name] to user [some user]

grant create table, create view, create function,create type to [some user]

go

Always confuses me to remember the grant alter and the create table portions.