Corrigindo o problema de usu�rios �rf�os no SQL Server

Motivaç�o

Quando se realiza a migraç�o de um banco de dados SQL Server para um novo servidor, � poss�vel que ap�s a restauraç�o do backup alguns usu�rios fiquem sem um login associado, problema que pode deix�-los sem acesso �s bases de dados. Esses usu�rios s�o ent�o chamados de �rf�os e por n�o entender esse problema, algumas vezes os respons�veis pelo banco de dados acabam criando novas credenciais para voltar a ter acesso aos bancos de dados.

Neste artigo, veremos como identificar esses usu�rios e como corrigir esse problema.

Saiba mais sobre: SQL Server

Contextualizaç�o: Entendendo Logins e Usu�rios

Login � um objeto que garante acesso em n�vel de inst�ncia do SQL Server. Para listar todos os logins existentes, podemos utilizar o seguinte script:

SELECT * FROM SYS.server_principals

J� um usu�rio fornece acesso aos objetos em n�vel de banco de dados no SQL Server, e pode ou n�o estar vinculado a um login, dependendo da pol�tica do seu ambiente. Para listar todos os usu�rios de um banco de dados, podemos realizar a seguinte consulta:

SELECT * FROM SYS.sysusers

Ao criarmos um login no SQL Server, ele recebe automaticamente uma identificaç�o, chamada de SID e que estar� armazenada na tabela SYS.sysusers, informando a qual login pertence determinado usu�rio. Assim, podemos fazer uma junç�o entre as tabelas vistas acima, com a instruç�o da Listagem 1, e listar os usu�rios com suas respectivas associaç�es aos logins. O resultado pode ser visto na Figura 1.

Listagem 1. Retornando logins e usu�rios associados pelo SID


        SELECT U.name as USUARIO
        , L.name AS LOGIN
        , U.sid AS SID_USER
        , L.sid AS SID_LOGIN
        FROM SYS.sysusers U
        INNER JOIN SYS.server_principals L
        ON U.sid = L.sid
        WHERE L.name = "lg_devmedia" -- Filtro aqui para retornar somente 1 Login.

Figura 1. Associaç�o Login x Usu�rio via T-SQL

Esses mesmos dados tamb�m podem ser verificados no SQL Server Management Studio ao abrir o n� Databases, expandir a opç�o Security e clicar com o bot�o direito do mouse em Users > Properties. Na aba General, que � exibida na Figura 2, vemos os detalhes dos usu�rios.

Figura 2. Associaç�o de login e usu�rio no SQL Server Management Studio

Saiba mais sobre: logins, usu�rios e permiss�es no SQL Server

Problema: Entendendo o usu�rio �rf�o

Conhecendo os conceitos de usu�rios �rf�os e como identific�-los, podemos entender agora o cen�rio em que esse problema surge: ao realizar um backup, o SQL Server carrega apenas os objetos do banco de dados (e n�o da inst�ncia inteira). Assim, os usu�rios s�o inclu�dos no arquivo, mas os logins, n�o.

Na Figura 3 temos um exemplo de ambiente com duas inst�ncias (a principal em vermelho e a que receber� o backup em verde) e a base DEVMEDIA que ser� migrada.

Figura 3. Ambiente para simular o problema

Ap�s um backup no servidor antigo e um restore no novo, verificaremos nas tabelas de usu�rios e logins uma nova situaç�o para o usu�rio u_devmedia que, de acordo com a Figura 1, possu�a um login associado normalmente. Agora, no entanto, ao realizarmos a consulta da Listagem 2, veremos que o usu�rio se tornou �rf�o, pois os campos LOGIN e SID_LOGIN est�o nulos, como mostra a Figura 4.

Listagem 2. Verificando a n�o associaç�o de um login ao usu�rio


        SELECT U.name as USUARIO
        , L.name AS LOGIN
        , U.sid AS SID_USER
        , L.sid AS SID_LOGIN
        FROM SYS.sysusers U
        LEFT JOIN SYS.server_principals L
        ON U.sid = L.sid
        WHERE u.name = "u_devmedia"

Figura 4. Usu�rio �rf�o

Saiba mais sobre: Como realizar backups no SQL Server

Soluç�o 1: Associando o usu�rio a um login existente

Nesta primeira forma de resolver o problema, vamos criar um login e depois associ�-lo ao usu�rio �rf�o, utilizando para isso o script da Listagem 3.

Listagem 3. Criando um novo Login


        CREATE LOGIN [lg_devmedia] WITH PASSWORD=N"1",
        CHECK_EXPIRATION=OFF,
        CHECK_POLICY=OFF
        GO

Linha 2: a cl�usula CHECK_EXPIRATION=OFF informa que a senha n�o ir� expirar;
Linha 3: a cl�usula CHECK_POLICY=OFFdiz que a senha n�o precisar� ter uma pol�tica de segurança forte.

Criado o login, precisamos apenas associ�-lo a um usu�rio utilizando o seguinte script:


        ALTER USER u_devmedia WITH LOGIN = [lg_devmedia]

Soluç�o 2: Associando o SID do usu�rio ao login

Nesta segunda forma, iremos pegar o SID da tabela SYS.sysusers (que pode ser obtido com o script da Listagem 2) e acrescent�-lo j� na criaç�o do login, conforme a Listagem 4.

Listagem 4. Criando login com o SID do usu�rio


        CREATE LOGIN [lg_devmedia] WITH PASSWORD=N"1",
        CHECK_EXPIRATION=OFF,
        CHECK_POLICY=OFF,
        SID = 0x902B3E8B5639F644AC70976096AB9B73 --SID do usu�rio
        GO

Feito isso, execute novamente a consulta da Listagem 1 e verifique que agora a associaç�o entre usu�rio e login foi reestabelecida, garantindo o pleno acesso �s bases de dados.