Início / dba / Perguntas / 311122
Accepted
SEarle1986
Asked: 2022-04-20 14:25:36 +0800 CST

SET SINGLE_USER WITH ROLLBACK IMMEDIATE apenas desconecta sessões quando a conta é um usuário do banco de dados

  • 772

O código a seguir cria um usuário capaz de restaurar um banco de dados:

CREATE LOGIN RestoreUser WITH PASSWORD = 'MyPassword'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

Isso funciona bem e o usuário pode restaurar o banco de dados, no entanto, se eu quiser definir o banco de dados SINGLE_USERe reverter quaisquer conexões existentes, o comando de restauração será bloqueado e falhará:

Sessão 1

/* SELECT from a table and leave the SSMS window open, leaving a sleeping SPID */
USE AdventureWorks2014
SELECT * FROM Person.Person

Sessão 2

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

Eventualmente, a sessão 2 expira com

Msg 5061, Level 16, State 1, Line 3
ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks2014'. Try again later.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

A sp_whoisactivecaptura de tela a seguir mostra que o comando está bloqueado:

insira a descrição da imagem aqui

O sp_whoisactiverelatório de bloqueio para a sessão 59 mostra

<Database name="AdventureWorks2014">
  <Locks>
    <Lock request_mode="X" request_status="CONVERT" request_count="1" />
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
    <Lock request_mode="U" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="(null)">
      <Locks>
        <Lock resource_type="DATABASE.BULKOP_BACKUP_DB" request_mode="U" request_status="GRANT" request_count="1" />
        <Lock resource_type="DATABASE.BULKOP_BACKUP_LOG" request_mode="S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

O artigo da Microsoft para SET SINGLE_USER afirma

Para obter acesso exclusivo rapidamente, o exemplo de código usa a opção de encerramento WITH ROLLBACK IMMEDIATE. Isso fará com que todas as transações incompletas sejam revertidas e quaisquer outras conexões com o banco de dados AdventureWorks2012 sejam imediatamente desconectadas.

e

Requer permissão ALTER no banco de dados.

Minha RestoreUserconta tem as permissões corretas ( dbcreatora função de servidor fornece ALTER ANY DATABASEpermissões), mas não sei por que o comando está bloqueado, a primeira citação sugere que todas as outras conexões seriam desconectadas.

Eu também concedi ALTER ANY CONNECTION, RestoreUsermas isso não ajudou.

O que encontrei resolveu o problema, é se RestoreUserum usuário no banco de dados está sendo restaurado (não precisa de permissões no banco de dados)

CREATE LOGIN RestoreUser WITH PASSWORD = 'ABC@123'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

USE AdventureWorks2014
CREATE USER RestoreUser

O código

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

Em seguida, é executado sem ser bloqueado pela sessão de suspensão.

Minhas perguntas são

  • Por que o SET SINGLE_USER WITH ROLLBACK IMMEDIATEcódigo é bloqueado quando deveria matar e reverter todos os SPIDs abertos
  • Por que criar o usuário no banco de dados corrige esse problema?
restore sql-server-2016

1 respostas

  1. Best Answer

    Como Dan Guzman observou originalmente, a sp_WhoIsActivesaída indica que o RESTOREestá bloqueado, não o ALTER DATABASEcomando. Mesmo assim:

    Se o SET SINGLE_USERcomando for incontestado , não há problema. A conexão adquire o SESSIONbloqueio de banco de dados compartilhado de nível único que impede que qualquer outra pessoa se conecte ao banco de dados, e a vida é boa.

    Se houver contenção (outra conexão com o banco de dados), há uma verificação para ver se a entidade de segurança que executa o comando pode se conectar ao banco de dados.

    Quando essa verificação falha, o ALTER DATABASEcomando falha e uma mensagem de erro como a seguinte é retornada:

    Msg 5061, Level 16, State 1, Line 10 ALTER DATABASE falhou porque um bloqueio não pôde ser colocado no banco de dados 'AdventureWorks2017'. Tente mais tarde.
    Msg 5069, Nível 16, Estado 1, Linha 10 A instrução ALTER DATABASE falhou.

    A verificação é bem-sucedida quando:

    • O principal tem CONNECT ANY DATABASEpermissão; ou
    • O principal tem um usuário relacionado no banco de dados; ou
    • O usuário convidado no banco de dados tem CONNECTpermissão.

    Não sei por que essa verificação existe ou por que ela só ocorre se houver conexões bloqueando. Pode haver uma boa razão, ou pode ser um teste impreciso ou obsoleto.

    De certa forma, é estranho que uma sessão possa adquirir o único SESSIONbloqueio de banco de dados compartilhado associado a um único usuário quando o principal não tem direito de se conectar ao banco de dados.

    Observe também que o mesmo erro ocorre pelo mesmo motivo se você tentar definir o banco de dados MULTI_USERquando ele já estiver nesse estado e houver outros usuários conectados ao banco de dados.

    Por outro lado, pode-se argumentar que ALTER ANY DATABASEnão atende ao requisito que você citou para ALTERpermissão no banco de dados específico. Ter ALTERpermissão no banco de dados implica um usuário nesse banco de dados porque essa permissão só pode ser atribuída a usuários, não a logins. Isso é bastante persuasivo, mas não explica por que só deve ser testado quando ocorre a contenção.

    Além disso, o ALTER DATABASEcomando no seu exemplo gera um erro, mas você não verifica ou testa se o banco de dados está no modo de usuário único antes de continuar com a restauração.

    • 3

relate perguntas