Eu herdei um sistema onde o aplicativo está sendo executado em sysadminconta. Limitei essa conta a db_datareader+ db_datawriter+ EXECUTEem todo banco de dados e configurei extended eventsa sessão para pegar o permission errorsno servidor.
Foi muito surpreendente para mim ver algumas insertsfalhas, mesmo que o usuário seja membro db_datawritere nenhuma restrição granular nas tabelas seja aplicada.
Então observo que apenas as inserções que definiram IDENTITY_INSERTfalharam. O banco de dados em questão está cheio identitye há muito SET IDENTITY_INSERTem seu código. O código significa não apenas modulesarmazenado no servidor, mas também C#código.
Para poder definir identity_inserto usuário deve possuir a tabela ou ter ALTERpermissão na tabela. Mas o fato é que você NÃO PODE conceder ALTERapenas em tabelas , sou obrigado a conceder ALTERem um banco de dados inteiro para isso user
(vou tornar esse usuário membro de db_ddladminrole que adicionará apenas 42 permissões(!!!) vs 51 permissões que poderiam ser adicionado às permissões do usuário, concedendo ALTERno banco de dados)
Não estou interessado em refatorar o banco de dados com sequence(a versão do servidor é 2014para que eu possa fazê-lo teoricamente) e não posso simplesmente adicionar execute as dboa todos os sp que uso identity_insertporque também há código de aplicativo para reescrever, só me pergunto por que a Microsoft faz algo tão estranho design de permissões que db_datawriternão pode definir identity_insert?
Existe outra maneira de fazer com que o usuário possa definir identity_insertque adicionará menos permissões do que db_ddladmin?
ATUALIZAR
Eu tentei a solução oferecida pelo LowlyDBA com 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;
Isso causa o erro
Msg 1088, Level 16, State 11, Line 1 Não é possível localizar o objeto "dbo.t" porque ele não existe ou você não tem permissões.
A solução de Antoine Hernandezconceder ao usuário ALTER apenas no esquema parece ser o menor dos males, então eu aceito
Sugiro que você crie uma função de banco de dados personalizada clicando com o botão direito do mouse na pasta de função de banco de dados.
Isso exibirá a função de banco de dados que você pode personalizar. Na primeira tela, você pode dar à função o nome e o proprietário de sua escolha. Você também pode adicionar o usuário que deseja que seja membro dessa função.
Feito isso, você pode clicar em Securables para poder escolher o que conceder.
Adicionar objetos aparecerá e sugiro escolher a opção "Todos os objetos dos tipos..." e clicar em OK.
Em Select Object Types, role para baixo para localizar Schemas, marque a caixa e clique em OK.
Agora você verá uma lista de esquemas, então escolha o(s) esquema(s) que possui os objetos aos quais você deseja conceder permissões e ele mostrará as permissões para esse esquema abaixo.
Para este exemplo, escolhi o esquema dbo. Redimensionei a tela padrão para mostrar mais de uma vez.
Nesse caso, eu provavelmente escolheria conceder Alter porque, de acordo com a Microsoft , "quando concedido em um escopo, ALTER também concede a capacidade de alterar, criar ou descartar (ênfase minha) qualquer protegível contido nesse escopo ". Com base no infográfico sobre permissões de banco de dados, localizado aqui e abaixo , conceder Alter no esquema concederá alter em Agregado, Padrão, Função, Procedimento, Fila, Regra, Sinônimo, Tabela e Visualização:
Como estou neste estágio, para essa mesma função, provavelmente também concederia Execute também para que o membro da função também pudesse executar qualquer procedimento armazenado para que, quando novas tabelas ou procedimentos armazenados fossem adicionados ao banco de dados, eu não precisaria modificar nenhum segurança para permitir que os novos objetos funcionem como os objetos existentes. Assim, as seleções ficariam assim depois de concluídas:
Eu poderia ter dado um passo adiante e também concedido Select, Insert, Update e Delete, e provavelmente teria coberto tudo o que a função exigiria para ler, inserir, excluir e atualizar qualquer tabela, bem como executar qualquer procedimento armazenado sem precisar conceder todas as outras permissões que a função db_ddladmin concede. Ao fazer isso no nível do esquema, ele pode facilitar o gerenciamento da segurança de algumas maneiras, especialmente quando um usuário específico precisa ter muito, se não todo o acesso no nível do esquema, mas também satisfaz o requisito de não conceder alteração ao todo. base de dados. Se houver outras permissões que essa configuração concede às quais a função não precisa, você poderá adicionar permissões DENY conforme necessário.
Na pergunta, o OP afirma que não é possível conceder ALTER apenas a uma tabela. Talvez eu tenha entendido errado alguma coisa. Mas descobri que no SQL Management Studio para um usuário de banco de dados em Securables posso adicionar uma única tabela e, em seguida, para Permissões na linha Alter ativar Grant. IDENTITY_INSERT ON corre felizmente depois disso. Isso está no SQL-Server2012 (v13) para um usuário de banco de dados "IIS APPPOOL\xyz" que por padrão pode selecionar, executar, atualizar, inserir, excluir.
Estou longe de ser um especialista nisso. Apenas tentei. Alguém pode comentar se isso realmente apenas adiciona a capacidade de modificar o esquema da respectiva tabela? Eu poderia viver com isso.