{"id":2800,"date":"2014-09-29T09:27:30","date_gmt":"2014-09-29T12:27:30","guid":{"rendered":"http:\/\/blog.dialhost.com.br\/?p=2800"},"modified":"2014-09-29T09:27:30","modified_gmt":"2014-09-29T12:27:30","slug":"sql-server-e-o-classico-problema-dos-usuarios-orfaos","status":"publish","type":"post","link":"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/","title":{"rendered":"SQL Server e o cl\u00e1ssico problema dos usu\u00e1rios \u00f3rf\u00e3os"},"content":{"rendered":"

\t\t\t\t <\/p>\n

\"SQL<\/a>
SQL Server<\/figcaption><\/figure>\n

Se voc\u00ea j\u00e1 tem alguma experi\u00eancia com SQL Server, muito provavelmente j\u00e1 teve problemas com uma conta de usu\u00e1rio que deixa de funcionar. Geralmente isso acontece ap\u00f3s a execu\u00e7\u00e3o de uma restaura\u00e7\u00e3o da base que est\u00e1 recusando a conex\u00e3o. Este \u00e9 um problema cl\u00e1ssico do SQL Server, conhecido como \u201cusu\u00e1rio \u00f3rf\u00e3o\u201d.<\/p>\n

Muita coisa j\u00e1 foi publicada sobre este tema. Este artigo discute por qu\u00ea ainda temos este problema depois de quase 20 anos de exist\u00eancia do SQL Server.<\/p>\n

Um pouco de Hist\u00f3ria<\/strong><\/p>\n

Segundo a Wikipedia, a hist\u00f3ria do SQL Server come\u00e7a em 1989. Particularmente, eu prefiro dizer que a Microsoft lan\u00e7ou seu produto comercialmente em 1998. Antes disso, o que havia era uma vers\u00e3o do Sybase adaptada para Windows, fruto de uma parceria muito obscura da Microsoft com a empresa Sybase, que naturalmente terminou com um processo judicial.<\/p>\n

Em 1998, j\u00e1 encerrada esta estranha parceria, a Microsoft lan\u00e7ou o SQL Server 7.0, que tinha semelhan\u00e7as enormes com o produto Sybase, se \u00e9 que voc\u00ea me entende\u2026 Muita coisa foi herdada e continua assim at\u00e9 hoje. J\u00e1 no seu lan\u00e7amento, o SQL Server 7.0 dispunha do chamado \u201cmodo de autentica\u00e7\u00e3o mista\u201d. Isso quer dizer que o usu\u00e1rio poderia se logar usando autentica\u00e7\u00e3o do pr\u00f3prio SGBD (login + senha) ou ent\u00e3o com autentica\u00e7\u00e3o pelo Windows (reconhecimento do usu\u00e1rio de Windows logado no dom\u00ednio).<\/p>\n

Por\u00e9m, o modelo de seguran\u00e7a foi herdado do Sybase e permanece assim at\u00e9 hoje. Diferentemente de outros SGBDs, SQL Server e Sybase usam duas entidades distintas:<\/p>\n

\u201clogin\u201d: que autentica o usu\u00e1rio na inst\u00e2ncia
\n\u201cuser\u201d: que controla os privil\u00e9gios do usu\u00e1rio dentro de cada banco de dados
\nE \u00e9 este modelo de seguran\u00e7a que d\u00e1 origem ao problema dos usu\u00e1rios \u00f3rf\u00e3os. Por conta disso, SQL Server e Sybase s\u00e3o igualmente afetados por este problema.<\/p>\n

Entendendo os usu\u00e1rios \u00f3rf\u00e3os<\/strong><\/p>\n

O problema come\u00e7a pelo fato dos logins serem gravados na base de sistema Master, enquanto os usu\u00e1rios (ou \u201cusers\u201d se preferir) s\u00e3o armazenados em cada base em que eles t\u00eam acesso. A vis\u00e3o MASTER.SYS.SYSLOGINS informa detalhes do login: n\u00famero do identificador de seguran\u00e7a (SID), data de cria\u00e7\u00e3o, senha (se usar autentica\u00e7\u00e3o SQL), autoridades a atribu\u00eddas ao n\u00edvel da inst\u00e2ncia etc. J\u00e1 a vis\u00e3o NomeBD.SYS.SYSUSERS informa detalhes do usu\u00e1rio: identificador do usu\u00e1rio (UID), data de cria\u00e7\u00e3o e, entre v\u00e1rios outros detalhes, o SID associado.<\/p>\n

Quando usamos a autentica\u00e7\u00e3o do Windows, o SGBD transfere a tarefa de autentica\u00e7\u00e3o de usu\u00e1rio para o pr\u00f3prio Windows. Portanto, o SQL Server precisa apenas identificar os logins que est\u00e3o cadastrados, reaproveitando o SID j\u00e1 usado no dom\u00ednio para aquele login.<\/p>\n

Por\u00e9m a coisa muda quando usamos autentica\u00e7\u00e3o SQL. Ao criarmos um novo login, o SGBD vai gerar um novo SID para este login. Ocorre que as inst\u00e2ncias do SQL Server s\u00e3o independentes, n\u00e3o havendo nenhum tipo de integra\u00e7\u00e3o entre os SIDs cadastrados em cada inst\u00e2ncia.<\/p>\n

Deste modo, quando criamos o mesmo login em duas ou mais inst\u00e2ncias (por exemplo, as inst\u00e2ncias SQL2012DEV e SQL2012PROD, referentes a desenvolvimento e produ\u00e7\u00e3o, obviamente), ser\u00e3o gerados SIDs diferentes. Quando fizermos um backup da base BDTESTE de produ\u00e7\u00e3o e a restaurarmos na inst\u00e2ncia de desenvolvimento, a vis\u00e3o BDTESTE.SYS.SYSUSERS trar\u00e1 o SID que era usado em produ\u00e7\u00e3o e que n\u00e3o tem nenhuma correspond\u00eancia com aquele usado na inst\u00e2ncia desenvolvimento.<\/p>\n

E assim encontramos o famoso usu\u00e1rio \u00f3rf\u00e3o. Neste quadro, o login continuar\u00e1 funcionando normalmente e o dono daquele login conseguir\u00e1 conectar em todas as bases\u2026 Exceto na base BDTESTE, que acabou de ser restaurada.<\/p>\n

Corrigindo a situa\u00e7\u00e3o<\/strong><\/p>\n

Este problema foi solucionado desde os prim\u00f3rdios do SQL Server, usando a rotina SP_CHANGE_USERS_LOGIN.<\/p>\n

Este \u00e9 um procedimento em dois passos. No primeiro, identificamos quem s\u00e3o os usu\u00e1rios \u00f3rf\u00e3os da base, rodando o procedimento usando o par\u00e2metro \u201cReport\u201d. Feito isso, podemos escolher entre tr\u00eas caminhos:<\/p>\n

    \n
  1. Se o login e o \u201cuser\u201d tem o mesmo nome, usamos os par\u00e2metros \u201cAutofix\u201d e o nome do usu\u00e1rio.<\/li>\n
  2. Se login e \u201cuser\u201d forem diferentes, ent\u00e3o devemos usar tr\u00eas par\u00e2metros, que s\u00e3o a a\u00e7\u00e3o \u201cUpdate_One\u201d, o nome do \u201cuser\u201d e o nome do login correspondente.<\/li>\n
  3. Se n\u00e3o existe login adequado, ent\u00e3o devemos usar quatro par\u00e2metros, que s\u00e3o a a\u00e7\u00e3o \u201cUpdate_One\u201d, o nome do \u201cuser\u201d e o nome do login a ser criado e a senha deste novo login.<\/li>\n<\/ol>\n

     <\/p>\n

    A listagem 1 mostra este exemplo:<\/p>\n

    \n\n\n\n
    1<\/code><\/td>\nEXEC sp_change_users_login 'Report'<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    2<\/code><\/td>\n--alternativa 1<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    3<\/code><\/td>\nEXEC sp_change_users_login 'Auto_Fix', 'NomeUsuario'<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    4<\/code><\/td>\n-- alternativa 2<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    5<\/code><\/td>\nEXEC sp_change_users_login ' Update_One', 'NomeUsuario', 'LoginDiferente'<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    6<\/code><\/td>\n-- alternativa 3<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    7<\/code><\/td>\nEXEC sp_change_users_login ' Update_One', 'NomeUsuario', 'NovoLogin\u2019, 'Senha'<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

     <\/p>\n

    Como evitar \u00f3rf\u00e3os<\/strong><\/p>\n

    Uma forma de evitar os usu\u00e1rios \u00f3rf\u00e3os \u00e9 usar apenas autentica\u00e7\u00e3o pelo Windows. Desta forma, o SQL Server n\u00e3o controla e n\u00e3o autentica nada. Quem faz isso \u00e9 o sistema operacional. Esta \u00e9 a estrat\u00e9gia usada pelo DB2, por exemplo.<\/p>\n

    O modelo de seguran\u00e7a do SQL Server \u00e9 desenhado para trabalhar com inst\u00e2ncias independentes com m\u00faltiplas bases de dados. E isso realmente complica o uso de autentica\u00e7\u00e3o SQL.<\/p>\n

    Se desej\u00e1ssemos usar autentica\u00e7\u00e3o SQL e n\u00e3o termos problemas com usu\u00e1rios orf\u00e3os, seria necess\u00e1rio haver um reposit\u00f3rio central de logins usados em todas as inst\u00e2ncias SQL no dom\u00ednio, de modo que houvesse um \u00fanico SID para cada login em todas as inst\u00e2ncias SQL.<\/p>\n

    Conclus\u00e3o<\/strong><\/p>\n

    Ter usu\u00e1rios \u00f3rf\u00e3os \u00e9 o pre\u00e7o que se paga para o SQL Server trabalhar ao mesmo tempo com a ideia de inst\u00e2ncias com m\u00faltiplas bases e tamb\u00e9m oferecer dois tipos de autentica\u00e7\u00e3o diferentes (Windows ou SQL).<\/p>\n

    A solu\u00e7\u00e3o do problema \u00e9 bem simples, como vimos aqui. E na minha opini\u00e3o, este \u00e9 um custo baixo pelas funcionalidades que o SQL Server oferece.<\/p>\n

    Leituras Sugeridas<\/strong><\/p>\n

    NM, Vinoth.\u00a0 Find Orphan SQL Users and Fix using ALTER USER. TOAD WORLD. 24\/10\/2013<\/a>.
    \n    BERRY, Glenn. How To Avoid Orphaned Database Users with SQL Server Authentication.\u00a0 SQLSKILLS. 23\/08\/2013.<\/a>
    \n    SHEHZAD, Atif. Understanding and dealing with orphaned users in a SQL Server database.\u00a0 MSSQLTIPS. 24\/09\/2008.<\/a>
    \n    CAMPBELL, Michael K.\u00a0 SQL Server 2012 Contained Databases.\u00a0 SQL SERVER PRO. 20\/09\/2012. http:\/\/sqlmag.com\/sql-server-2012\/sql-server-2012-contained-databases<\/a><\/p>\n

    —-<\/p>\n

    Artigo de Wagner Crivelini, publicado originalmente no iMasters<\/a>.\t\t\t\t<\/p>\n","protected":false},"excerpt":{"rendered":"

    \t\t\t\tSe voc\u00ea j\u00e1 tem alguma experi\u00eancia com SQL Server, muito provavelmente j\u00e1 teve problemas com uma conta de usu\u00e1rio que deixa de funcionar.\t\t\t\t<\/p>\n","protected":false},"author":1,"featured_media":2811,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[40,99,158,191,192],"yoast_head":"\nSQL Server e o cl\u00e1ssico problema dos usu\u00e1rios \u00f3rf\u00e3os - Blog DialHost<\/title>\n<meta name=\"description\" content=\"Se voc\u00ea j\u00e1 tem alguma experi\u00eancia com SQL Server, muito provavelmente j\u00e1 teve problemas com uma conta de usu\u00e1rio que deixa de funcionar.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/\" \/>\n<meta property=\"og:locale\" content=\"pt_BR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server e o cl\u00e1ssico problema dos usu\u00e1rios \u00f3rf\u00e3os - Blog DialHost\" \/>\n<meta property=\"og:description\" content=\"Se voc\u00ea j\u00e1 tem alguma experi\u00eancia com SQL Server, muito provavelmente j\u00e1 teve problemas com uma conta de usu\u00e1rio que deixa de funcionar.\" \/>\n<meta property=\"og:url\" content=\"\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog DialHost\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/DialHost\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/DialHost\" \/>\n<meta property=\"article:published_time\" content=\"2014-09-29T12:27:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dialhost.com.br\/blog\/wp-content\/uploads\/2019\/04\/og-image.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"630\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dialhost\" \/>\n<meta name=\"twitter:site\" content=\"@dialhost\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#organization\",\"name\":\"DialHost Internet\",\"url\":\"https:\/\/www.dialhost.com.br\/blog\/\",\"sameAs\":[\"http:\/\/www.facebook.com\/DialHost\",\"https:\/\/www.instagram.com\/dialhost_oficial\/\",\"https:\/\/www.linkedin.com\/company\/dialhost-internet\",\"https:\/\/www.youtube.com\/channel\/UCAiqiX8IiqTSfA8wQhpzgXQ\",\"https:\/\/twitter.com\/dialhost\"],\"logo\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#logo\",\"inLanguage\":\"pt-BR\",\"url\":\"https:\/\/www.dialhost.com.br\/blog\/wp-content\/uploads\/2019\/03\/marca-dialhost.png\",\"width\":81,\"height\":81,\"caption\":\"DialHost Internet\"},\"image\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#logo\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#website\",\"url\":\"https:\/\/www.dialhost.com.br\/blog\/\",\"name\":\"Blog DialHost\",\"description\":\"Os melhores conte\\u00fados sobre Desenvolvimento Web, Marketing e Neg\\u00f3cios\",\"publisher\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.dialhost.com.br\/blog\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"pt-BR\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#primaryimage\",\"inLanguage\":\"pt-BR\",\"url\":\"\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#webpage\",\"url\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/\",\"name\":\"SQL Server e o cl\\u00e1ssico problema dos usu\\u00e1rios \\u00f3rf\\u00e3os - Blog DialHost\",\"isPartOf\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#primaryimage\"},\"datePublished\":\"2014-09-29T12:27:30+00:00\",\"dateModified\":\"2014-09-29T12:27:30+00:00\",\"description\":\"Se voc\\u00ea j\\u00e1 tem alguma experi\\u00eancia com SQL Server, muito provavelmente j\\u00e1 teve problemas com uma conta de usu\\u00e1rio que deixa de funcionar.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#breadcrumb\"},\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"item\":{\"@type\":\"WebPage\",\"@id\":\"\/blog\/\",\"url\":\"\/blog\/\",\"name\":\"In\\u00edcio\"}},{\"@type\":\"ListItem\",\"position\":2,\"item\":{\"@type\":\"WebPage\",\"@id\":\"\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/\",\"url\":\"\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/\",\"name\":\"SQL Server e o cl\\u00e1ssico problema dos usu\\u00e1rios \\u00f3rf\\u00e3os\"}}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#webpage\"},\"author\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#\/schema\/person\/a0b10cbe35449dea173a06d4664f9fcc\"},\"headline\":\"SQL Server e o cl\\u00e1ssico problema dos usu\\u00e1rios \\u00f3rf\\u00e3os\",\"datePublished\":\"2014-09-29T12:27:30+00:00\",\"dateModified\":\"2014-09-29T12:27:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#webpage\"},\"publisher\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/sql-server-e-o-classico-problema-dos-usuarios-orfaos\/#primaryimage\"},\"keywords\":\"banco de dados,feed de parceiros,noticias,sql,sqlserver\",\"articleSection\":\"Dicas e Not\\u00edcias\",\"inLanguage\":\"pt-BR\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#\/schema\/person\/a0b10cbe35449dea173a06d4664f9fcc\",\"name\":\"DialHost Internet\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dialhost.com.br\/blog\/#personlogo\",\"inLanguage\":\"pt-BR\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/05feec2f99aef1c42b09aeaa637ee66f?s=96&r=g\",\"caption\":\"DialHost Internet\"},\"description\":\"H\\u00e1 mais de 18 anos proporcionando um servi\\u00e7o de hospedagem de sites voltado 100% na Experi\\u00eancia e Sucesso do Cliente, prezando a qualidade, transpar\\u00eancia e seguran\\u00e7a.\",\"sameAs\":[\"https:\/\/www.facebook.com\/DialHost\",\"https:\/\/www.instagram.com\/dialhost_oficial\/\",\"https:\/\/www.linkedin.com\/company\/dialhost-internet\",\"https:\/\/twitter.com\/dialhost\",\"https:\/\/www.youtube.com\/channel\/UCAiqiX8IiqTSfA8wQhpzgXQ\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","_links":{"self":[{"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/posts\/2800"}],"collection":[{"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/comments?post=2800"}],"version-history":[{"count":0,"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/posts\/2800\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/media\/2811"}],"wp:attachment":[{"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/media?parent=2800"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/categories?post=2800"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dialhost.com.br\/blog\/wp-json\/wp\/v2\/tags?post=2800"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}