The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Monday, November 29, 2021 Login
Public

Blog posts for the month of November,2021.
Grant Create Table Permissions to a Schema, but not DBO11/2/2021 1:48:31 PM

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.
Stored procedure can not return BIGINT11/2/2021 2:02:07 PM
SQL Server stored procedures can not return a big integer.

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:

dbcc checkident ([table name],reseed,-2147483646)
select max([column identity]),min([column identity]) from [table name] with (nolock)
select count(*) from [table name] with (nolock) where [column identity] < 0


 

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




Blog Home