none
Restore do DB + SPs e Views RRS feed

  • Pergunta

  • Ao efetuar o restore de um banco em outro servidor (resumo: SIDs diferentes), como ficam as permissões nas Stored Procedures e Views?! Além de redefinir o User Mapping para os bancos, preciso também remontar as permissões em SPs e Views?!

     

    Pergunto isto pois comumente faço backup de um banco e restauro em outro server (de testes). Em outro cenário, com SQL 2000, sempre que faço isso preciso abrir as SPs e "ticar" as permissões de EXEC para o determinado usuário... ...pelo menos são poucas views/sps, mas imaginem em um banco grande?!

     

     

    Um abraço!

    domingo, 19 de outubro de 2008 20:00

Respostas

  • Oi Hélio,

     

    Acho que eu não expliquei direito... Vamos tentar ser um pouco mais práticos.

     

    Suponha que você crie um login chamado LGN e que mapeie esse login para um banco chamado BD. No mapeamento, você irá criar um usuário LGN no banco BD (você poderá conferir se for na aba Security do banco BD). Então dizemos que o login LGN tem acesso ao banco BD, pois, para isso o usuário LGN existe e está mapeado para o login LGN.

     

    Quando você dá as permissões, você as concede para o usuário LGN. É incorreto dizer que as permissões são concedidas ao login LGN, pois, logins tem escopo de servidor e não de banco. O correto é dizer que as permissões foram concedidas ao usuário LGN. Claro que indiretamente o login LGN terá as permissões, pois, está mapeado para o usuário LGN no banco BD.

     

    Quando você fizer o backup da base BD, você levará para o backup, todos os objetos de banco de dados incluíndos as SPs, tabelas, Views e o usuários (inclusive o usuário BD). O usuário BD continuará com o SID apontando para o login LGN.

     

    Ao restaurar o banco BD em outro servidor, tudo o que foi copiado será restaurado inclusive o usuário LGN. Só que o usuário LGN apontará para um login LGN que possivelmente não exista no novo servidor e mesmo que você crie um login LGN no novo servidor, possivelmente esse login LGN terá um SID diferente. Então teremos um usuário LGN no banco que não aponta para o login LGN do novo servidor já que os SIDs não batem. Você pode confirmar isso se clicar sobre o usuário LGN na aba Security do banco BD. Você verá que a coluna login estará em branco, ou seja, aquele usuário não mapeia para nenhum login.

     

    As permissões ficam intactas e o usuário LGN continuará tendo as mesmas permissões de quando o backup foi feito. Quando você tentar mapear o login LGN do novo servidor para o banco restaurado, ele irá dizer que o usuário LGN já existe. De fato já existe, mas o ponteiro para o SID está errado. Se você excluir o usuário LGN do banco para cadastrá-lo novamente e assim dar permissão para o login LGN do novo servidor, automaticamente as permissões são perdidas, pois, se o usuário é excluído, não tem porque haver permissões para ele.

     

    Então o melhor seria dizer ao SQL Server que o usuário LGN do backup restaurado deve apontar para o login LGN do novo servidor. Para fazer isso você usa a procedure sp_change_users_login 'Update_One', 'LGN', 'LGN'. Agora o login LGN do novo servidor está mapeado para o usuário LGN do banco e como as permissões ficaram intactas não será preciso reconcedê-las.

     

    [ ]s,

     

    Gustavo

    terça-feira, 21 de outubro de 2008 09:38

Todas as Respostas

  • Boa Tarde Hélio,

     

    Alguns objetos tem escopo de banco de dados enquanto outros possuem escopo de servidor. São exemplos de objetos de escopo de banco de dados: Usuários, Stored Procedures, Tabelas, Índices, etc. São exemplos de objetos de escopo de servidor: Logins, Server Roles, etc.

     

    Quando você faz um backup de banco de dados, você levará para esse backup todos os objetos do escopo desse banco. Isso significa que estarão presentes no backup todos os usuários, tabelas, permissões, views, triggers, etc. Se o backup for concluído com sucesso, tudo daquele banco de dados estará no backup. Se o backup não for feito com sucesso, o mesmo representa um banco corrompido, mas não existe como fazer um backup deixando de lado alguns objetos e outros não.

     

    Como logins não representam o escopo de um banco de dados, um backup de banco de dados não levará os logins e senhas utilizados para o banco de dados. Assim, quando você restaura o banco de dados, os logins que acessavam aquele banco não são restaurados, pois sequer fizeram parte do backup (afinal o escopo é diferente).

     

    Ao restaurar o backup, as permissões estão intactas, mas os usuários do banco estão sem mapeamento para os logins (a menos que você restaure o backup na mesma instância). Então no banco, existem as permissões, existem os usuários e possivelmente os logins, mas os logins não estão mapeados para os usuários e os usuários não estão mapeados para os logins (chamamos isso de logins órfãos).

     

    Nesse caso, se você tentar dar permissão a um login no banco de dados restaurado, irá ocorrer um erro, dizendo que o usuário já existe (e ele de fato existe, só não está mapeado). Você pode tentar excluir o usuário do banco e criar um novo usuário para aquele login naquele banco, mas ao fazer isso, imediatamente as permissões são perdidas. Para banco com poucos objetos ou permissões mais genéricas, não há problema, mas para bancos com muitos objetos e muitas permissões individualizadas isso é um enorme problema.

     

    O mais correto (independente da quantidade de permissões), é que você associe o usuário a um login através do comando abaixo.

     

    exec sp_change_users_login 'Update_One', 'Usr', 'Lgn'

     

    Se o banco tem um usuário chamado "Usr" e você deseja associá-lo a um login "Lgn", basta rodar o comando abaixo no banco de dados de seu interesse. Normalmente o login e o usuário no banco terão o mesmo nome.

     

    [ ]s,

     

    Gustavo

    domingo, 19 de outubro de 2008 20:33
  • Então, como ficaria por exemplo a associação do usuário "user1" para um banco chamado "TESTE"  ?

     

    Nota: O usuário "user1" está presente nos dois servidores, mas claro, com SIDs diferentes (SQL Authentication).

     

    E quando você diz "associar o usuário ao login", o usuário já não é o login?!?!

     

     

    Abraço!

     

     

    segunda-feira, 20 de outubro de 2008 00:27
  • Olá Hélio,

     

    Um dos problemas em explicar permissionamento é que os termos usuário e logins são usados como se fossem a mesma coisa quando na verdade não são. Logins são necessários para autenticar-se no servidor, para chegar a um servidor SQL. Usuários são necessários para acessar um banco de dados específico, para executar uma SP ou ler uma tabela.

     

    Você poderá perceber que ao clicar na aba security do servidor verá os logins e que ao clicar na aba security do banco de dados verá os usuários. Para que você consiga acessar um objeto do banco de dados é necessário possuir um login, conectar-se ao servidor, e através desse login (se devidamente mapeado) conectar-se ao banco de dados e acessar um objeto.

     

    O que está presente nos dois servidores mas com um SID diferente é um login e não um usuário. Usuários ficam no banco de dados e após serem restaurados em outros servidores tem o seu mapeamento para o login perdido. Por isso que é necessário refazer o mapeamento (você poderá perceber que ao clicar no usuário após o backup, não haverá nenhum login associado).

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 20 de outubro de 2008 20:03
  • Certo, então de qualquer maneira eu terei que fazer o mapping dos usuários, e além disso abrir cada VIEW/SP e "mapeá-las" também?!

     

    Abraço!

    terça-feira, 21 de outubro de 2008 02:03
  • Oi Hélio,

     

    Acho que eu não expliquei direito... Vamos tentar ser um pouco mais práticos.

     

    Suponha que você crie um login chamado LGN e que mapeie esse login para um banco chamado BD. No mapeamento, você irá criar um usuário LGN no banco BD (você poderá conferir se for na aba Security do banco BD). Então dizemos que o login LGN tem acesso ao banco BD, pois, para isso o usuário LGN existe e está mapeado para o login LGN.

     

    Quando você dá as permissões, você as concede para o usuário LGN. É incorreto dizer que as permissões são concedidas ao login LGN, pois, logins tem escopo de servidor e não de banco. O correto é dizer que as permissões foram concedidas ao usuário LGN. Claro que indiretamente o login LGN terá as permissões, pois, está mapeado para o usuário LGN no banco BD.

     

    Quando você fizer o backup da base BD, você levará para o backup, todos os objetos de banco de dados incluíndos as SPs, tabelas, Views e o usuários (inclusive o usuário BD). O usuário BD continuará com o SID apontando para o login LGN.

     

    Ao restaurar o banco BD em outro servidor, tudo o que foi copiado será restaurado inclusive o usuário LGN. Só que o usuário LGN apontará para um login LGN que possivelmente não exista no novo servidor e mesmo que você crie um login LGN no novo servidor, possivelmente esse login LGN terá um SID diferente. Então teremos um usuário LGN no banco que não aponta para o login LGN do novo servidor já que os SIDs não batem. Você pode confirmar isso se clicar sobre o usuário LGN na aba Security do banco BD. Você verá que a coluna login estará em branco, ou seja, aquele usuário não mapeia para nenhum login.

     

    As permissões ficam intactas e o usuário LGN continuará tendo as mesmas permissões de quando o backup foi feito. Quando você tentar mapear o login LGN do novo servidor para o banco restaurado, ele irá dizer que o usuário LGN já existe. De fato já existe, mas o ponteiro para o SID está errado. Se você excluir o usuário LGN do banco para cadastrá-lo novamente e assim dar permissão para o login LGN do novo servidor, automaticamente as permissões são perdidas, pois, se o usuário é excluído, não tem porque haver permissões para ele.

     

    Então o melhor seria dizer ao SQL Server que o usuário LGN do backup restaurado deve apontar para o login LGN do novo servidor. Para fazer isso você usa a procedure sp_change_users_login 'Update_One', 'LGN', 'LGN'. Agora o login LGN do novo servidor está mapeado para o usuário LGN do banco e como as permissões ficaram intactas não será preciso reconcedê-las.

     

    [ ]s,

     

    Gustavo

    terça-feira, 21 de outubro de 2008 09:38