Tweeted twitter.com/StackDBAs/status/1197031970452180992
    Became Hot Network Question
4 added 131 characters in body
source | link

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

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

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

3 added 528 characters in body
source | link

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

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?

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

2 Changing title to better match the actual question
| link

Can SET IDENTITY_INSERT forces you to violate principle of least privilegebe allowed with less privileges than db_ddladmin?

1
source | link