I’ve inherited a system where the application is running under
I limited this account to
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
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
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
(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
Is there other way to make the user be able to set
identity_insert that will add less permissions than
I tried the solution offered by LowlyDBA with
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.
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 suggest you create a custom database role by right-clicking the database role folder.
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.
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:Since 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:
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.
In the question the OP claims that it’s not possible to grant ALTER to a table only. Maybe I misunderstand something. But I found that in SQL Management Studio for a Database User under Securables I can add a single table and then for Permissions in row Alter activate Grant. IDENTITY_INSERT ON happily runs after that. This is on SQL-Server2012 (v13) for an “IIS APPPOOLxyz” database user which by default can select, execute, update, insert, delete.
I am far from being an expert on this. Just tried. Can someone comment if this really only adds the ability to modify the schema of the respective table? I could live with this.