Can SET IDENTITY_INSERT Be Allowed with Less Privileges Than db_ddladmin?

identitypermissionsSecuritysql serversql server 2014

I've inherited a system where the application is running under sysadmin account.
I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to catch the permission errors on the server.
It was very surprising to me to see some inserts failed even if the user is a member of db_datawriter and no granular restrictions on tables are applied.

Then I note that only inserts that set IDENTITY_INSERT failed.
The database in question is full of identity and there is too much SET IDENTITY_INSERT in their code. The code means not only modules stored on server but also C# code.

To be able to set identity_insert user must own the table or have ALTER permission on the table. But the fact is that you CANNOT grant ALTER only on tables, I'm forced to grant ALTER on a whole database to this user
(I'll make this user member of db_ddladmin role that will add only 42 permissions(!!!) vs 51 permissions that could be added to user permissions by granting ALTER on database)

I'm not interested in refactoring the database with sequence (server version is 2014 so I could do it theoretically) and I cannot just add execute as dbo to every sp that use identity_insert because there is also application code to rewrite, I just wonder WHY does Microsoft make such a strange permissions design that db_datawriter cannot set identity_insert?

Is there other way to make the user be able to set identity_insert that will add less permissions than db_ddladmin?

UPDATE

I tried the solution offered by LowlyDBA with synonyms:

create table dbo.t(id int identity);
go

alter schema sch transfer dbo.t;
go

create synonym dbo.t for sch.t;
go

set IDENTITY_INSERT dbo.t on;

This causes the error

Msg 1088, Level 16, State 11, Line 1 Cannot find the object "dbo.t"
because it does not exist or you do not have permissions.

enter image description here

The solution of Antoine Hernandez to grant the user ALTER only on schema seems to be le lesser of evils, so I accept it

Best Answer

I suggest you create a custom database role by right-clicking the database role folder.

enter image description here

That will bring up the Database Role that you can then customize. On the first screen you can give the role the name and owner of your choosing. You also can add the user you want to be a member of this role. enter image description here

Once that is done, you can click on Securables so you can then choose what to grant. enter image description here

Add Objects will show up and I suggest picking the "All objects of the types..." option and click OK. enter image description here

In the Select Object Types, scroll down to find Schemas, check the box, and then click OK. enter image description here

Now you will see a list of schemas so choose the schema(s) that has the objects you want to grant permissions to and it will show the permissions for that schema below. enter image description here

For this example, I chose dbo schema. I resized the default screen to show more at once. enter image description here

In this case I would most likely choose to grant Alter because according to Microsoft, "When granted on a scope, ALTER also bestows the ability to alter, create, or drop (emphasis mine) any securable that is contained within that scope." Based on the infographic about database permissions, located here and below granting Alter on schema will grant alter on Aggregate, Default, Function, Procedure, Queue, Rule, Synonym, Table, and View:Microsoft link https://aka.ms/sql-permissions-posterSince I am at this stage, for this same role, I would probably also grant Execute as well so the role member could also execute any stored procedure so when new tables or stored procedures are added to the database, I would not have to modify any security to allow the new objects to work like the existing objects. So the selections would look like this once complete: enter image description here

I could have taken it a step further and also granted Select, Insert, Update, and Delete, and it most likely would have covered everything the role would require to read, insert, delete, and update any table as well as execute any stored procedure without needing to grant all the other permissions that the db_ddladmin role grants. By doing this at the schema level it can ease the management of security in some ways, especially when a particular user needs to have a lot of if not all access at the schema level, but also satisfies the requirement of not granting alter to the whole database. If there were other permissions this configuration grants that the role does not need, you could then go add DENY permissions as needed.

Related Question