Embedded Files
Data de postagem: May 25, 2011 12:48:42 PM
Problema
Colocação e restauração de bancos de dados de uma instância do servidor para o outro são tarefas comuns realizadas por um DBA. Depois de anexar ou restaurar um banco de dados, previamente criado e configurado logins no banco de dados não oferecem acesso. Os sintomas mais comuns deste problema é que o aplicativo pode enfrentar erros de login falhou ou você pode receber uma mensagem como o usuário já existe no banco de dados atual quando você tenta adicionar o login do banco de dados. Este é um cenário comum, ao executar um attach ou uma restauração, assim como você resolver isso?
Solução
Quando um banco de dados é movido de um servidor para outro o login ids armazenados no banco de dados mestre não se alinham com as identificações de login armazenados em cada banco de dados do usuário. Como mencionado anteriormente, alguns dos erros que você pode ver depois de anexar ou restaurar um banco de dados incluem:
Msg 229, Level 14, State 1
% S permissão negada no objeto% .* s, banco de dados% .* s, proprietário% .* s
ou
Microsoft SQL-DMO (ODBC SQLState: 42000) Erro 15023: usuário ou função '% s' já existe no banco de dados atual.
Sem a devida compreensão e planejamento adequado você pode enfrentar esse problema. Você pode apagar e recriar o usuário, mas vai perder todas as permissões configuradas. Assim, um mecanismo adequado de vinculação é necessária, para que as permissões serão mantidas.
Algumas mensagens de erro mais possível que você pode ver incluem
Antes de chegar à solução para este problema, seria melhor ter um olhar sobre a questão backend. logons do SQL Server, que são armazenados no banco de dados mestre são mapeados em bancos de dados individuais. logins de acesso ao SQL Server bancos de dados individuais usando um banco de dados do usuário que está mapeado para o logon do SQL Server apropriado. Há duas excepções, a conta de convidado e membros de grupo do Microsoft Windows.O SQL Server 2005 logins de uma instância de servidor são visíveis na sys.server_principals Catálogo de vista do sistema ea visão sys.syslogins. Para o SQL Server 2000, você pode obter informações de login do SQL Server na tabela sysxlogins.
Por outro lado, as informações de mapeamento para um usuário de banco de dados é armazenada no banco de dados na tabela de sistema sysusers. Ele inclui o nome do usuário do banco de dados eo SID do login do SQL Server correspondente. As permissões do usuário do banco de dados são utilizados para fins de autorização no banco de dados.
Assim, podemos dizer que toda vez que criar um login do SQL Server, ele será visto no catálogo de vista do sistemasys.server_principals ou exibições sys.syslogins no SQL Server 2005 ou na tabela sysxlogins para o SQL Server 2000. As entradas no tabela sysusers de um banco de dados estão ligados a logons do SQL Server mostrado na imagem acima. Este link é criado através de uma coluna chamada SID.
Se conseguirmos mudar nossa base de dados para outra instância do SQL Server através de qualquer processo, o novo servidor pode ou não ter os mesmos login e os SIDs dos logins provavelmente seria diferente do SID desses logins no servidor original. O que isto significa é que, a tabela sysusers no banco de dados tem movido SIDs que não correspondem com as informações de login em banco de dados mestre sobre este novo servidor. Assim ficamos órfãos usuários.
Como exemplo, eu tenho criado e configurado quatro usuários com permissões no AdventureWorks banco de dados. Os usuários são UsuárioTeste1, TestUser2, TestUser3 e TestUser4. Quando eu restaurei o backup do banco de dados para outra instância do SQL Server 2005, embora os utilizadores que estavam presentes no banco de dados AdventureWorks e os logins existe no novo servidor, mas nenhum desses logins teve acesso ao banco de dados recém-restaurado.
Então, mantendo este cenário em mente, vamos executar algumas consultas para ver a diferença entre os SID de logon do SQL Server (se o logon do SQL Server está presente) e usuário do banco de dados de SID para TestUser3.
Script para ver a diferença em SID
USE MASTER
GO
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO
USE AdventureWorks
GO
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestUser3'
GO
O resultado abaixo mostra o SID do logon do SQL Server e do banco de dados ID de usuário diferente e é isso que causa o problema.
Agora que temos uma melhor compreensão do problema, é hora de chegar a alguns comandos úteis para análise e uma solução.
Tenho restaurado o banco de dados AdventureWorks de uma instância para outra com quatro usuários acima. Agora, para analisar a forma como muitos usuários órfãos existem no meu banco de dados restaurado, vou executar o comando T-SQL que produz uma lista de todos os usuários órfãos e no nosso caso todos os quatro usuários estão órfãos.
Comando para gerar a lista de usuários órfãos
USE adventureWorks
GO
sp_change_users_login @Action='Report'
GO
Agora que temos a lista dos usuários órfãos, podemos começar a resolver o problema. Para superar esse problema, você precisa vincular os SIDs dos usuários (a partir de sysusers) para os SIDs dos logins válidos no banco de dados mestre.O seguinte comando remapeia a conta de login do servidor especificado pelo UsuárioTeste1 com o usuário do banco de dados especificado pelo UsuárioTeste1.
Comando mapa para um usuário órfão
USE AdventureWorks
GO
sp_change_users_login @Action='update_one',
@UserNamePattern='TestUser1',
@LoginName='TestUser1'
GO
Ou se você tem certeza que o SQL Server nome de login é o mesmo que o usuário órfão do banco de dados que está sendo mapeada, então você pode usar um comando mais curto como este para TestUser2 ..
Comando mapa para um usuário órfão
EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO
Ambos os comandos irão mapear os usuários para os logins e eles não vão ficar órfãos por muito tempo.
Se um nome de login não existe, você teria de criá-lo primeiro antes de fazer o mapeamento. Uma maneira rápida de fazer isso é usar o seguinte comando que irá criar o login e mapeie o logon para o usuário.
Comando para mapear um usuário órfão de um login que não está presente, mas será criado
EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'
GO
Resumindo o T-SQL usado
No processo acima, o sp_change_users_login procedimento armazenado é utilizado. A variável [@ Action] especifica o uso exato deste procedimento armazenado. Ele aceita um parâmetro como varchar (10) e pode ter um dos seguintes valores:
Se o parâmetro é Auto_Fix, o usuário do banco de dados é mapeado com o login do SQL Server chamado mesmo. Pode também criar login, caso não esteja presente.
Se o parâmetro é o relatório, que enumera os usuários órfãos e seus identificadores de segurança (SID).
Se o parâmetro é Update_One, que liga o usuário do banco de dados especificado para um logon do SQL Server existente.
Algumas considerações
sp_change_users_login requer associação na função de banco de dados fixa db_owner. Somente membros da função de servidor fixa sysadmin pode especificar a opção Auto_Fix.
Quando o usuário órfão é mapeado, a SID no banco de dados mestre é atribuído ao usuário órfão, então toda vez que um DB é anexado ou restaurado o SID será diferente entre o SQL Server eo login do usuário do banco de dados.
Se você tem diferentes nomes de login Server mapeado para um usuário de banco de dados, então não use o comando com Auto_Fix para vincular
Um usuário pode se tornar órfão também se o login do SQL Server correspondente é descartado
Embora óbvio, mas é bom mencionar, após a re-ligação entre a senha de logon do SQL Server pode ser usado pelo usuário do banco de dados.
Próximos Passos
Agora que você tem uma melhor compreensão deste problema e como resolvê-lo usandosp_change_users_login, seria uma boa prática para preparar um roteiro para a detecção e ligação de usuários órfãos por todos os seus bancos de dados.
Page updated
Google Sites
Report abuse