4

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

  • If the code is in a stored procedure, sign the proc with a certificate. This is a good way to follow the security principle of least privilege, elevating only as needed. – Dan Guzman Nov 8 at 11:35
  • @Dan Guzman If a code was only on the server side in modules, "with execute as dbo" would sufficient. But T-SQL code is incorporated within C code – sepupic Nov 8 at 11:40
  • 1
    Indeed a strange decision by Microsoft - but even more so, that you can't simply insert a different value for the identity column ... I'm more used to mysql/mariadb where it's very common to insert a value for the auto_increment column if you want to fill "holes" in the index/pk – eagle275 Nov 8 at 12:25
  • You could create a role and give that role all the specific permissions you want them to have. On the other hand, if the application has a lot of SET Identity Insert code, why use identity at all? – Antoine Hernandez Nov 8 at 15:28
  • @Antoine Hernandez And what will be different if instead of user I grant the permissions to the role? That user will still have 42 permissions instead of one. To your second observation: this code is not supposed to be refactored, I said it in my question. The code cannot be touched at all, I need a solution on server side and without refactoring – sepupic Nov 8 at 15:41
6

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.

  • 1
    To further decrease the footprint of additional security settings, you could swap the existing tables that need IDENTITY_INSERT to a new schema and grant the ALTER only to this schema. Then make synonyms on the original schema that point to this new one so that no code need be updated. And of course any new tables should just not use this pattern at all. – LowlyDBA Nov 8 at 18:17
  • >>> I would most likely choose to grant Alter <<< I already wrote about it but I can repeat it again: by granting ALTER you'll grant 51(!!!) permissions at once, including ALTER ANY USER and ALTER ANY ROLE, it's a greate security hole. Membership in ddl_admin grants only 42 permissions excluding all security permission. In any case every time I need to create role/grant permissions I always use CODE, not GUI – sepupic Nov 8 at 18:22
  • @LowlyDBA This seemed to be a solution but 1) I'm not sure EF will work with synonyms, I alredy tried to give it a view with the same name as table had (I was trying to set up partitioned view) but EF complained about "table not found" – sepupic Nov 8 at 18:42
  • 2) I tried your solution in test environment, my test account with ALTER on another schema (sch) where the object resides could not set identity insert with the error Cannot find the object "dbo.t" because it does not exist or you do not have permissions. (dbo.t is a synonym for sch.t), and sysadmin was not able to set it with the error 8105: 'dbo.t' is not a user table. Cannot perform SET operation. – sepupic Nov 8 at 18:43
  • @sepupic All of these details would have been good to put upfront in the question (use of EF, trying view, etc.). I think you know you have very limited options here if you can't change code and are refusing the manual but very viable option of individual grants. You can't have your cake and eat it too with poorly designed architecture. – LowlyDBA Nov 8 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.