{"id":1385,"date":"2012-01-24T11:00:23","date_gmt":"2012-01-24T10:00:23","guid":{"rendered":"http:\/\/diablo.craem.net\/wordpress\/?p=348"},"modified":"2012-01-24T11:00:23","modified_gmt":"2012-01-24T10:00:23","slug":"eliminar-usuarios-huerfanos-en-sql-server-2008-2","status":"publish","type":"post","link":"https:\/\/diablo.craem.net\/?p=1385","title":{"rendered":"Eliminar usuarios &#034;hu\u00e9rfanos&#034; en sql server 2008"},"content":{"rendered":"<p>\t\t\t\t<![CDATA[Estos d\u00edas, he tenido que migrar un aplicativo en un cliente y ello conlleva la migraci\u00f3n del sql server 2000 a sql server 2008.\nno cabe decir que, me he tenido que buscar la vida \"bastante\" para migrar el sql..... el proveedor me d\u00e1 el CD de instalaci\u00f3n y un \"b\u00fascate la vida\" para hacerlo..... y la expresi\u00f3n: \" si tienes problemas con el SQL, te enviamos un t\u00e9cnico\".\nen fin, ten\u00eda que hacerlo s\u00ed o s\u00ed.\n1\u00ba) Instal\u00e9 el sql server en otro servidor\n2\u00ba) Hice copia de seguridad de las BBDD de SQL 2000\n3\u00ba) Recuper\u00e9 las copias\nAhora llega el problema... ten\u00eda que crear los usuarios en el SQL server 2008 y problema.... los usuarios ya exist\u00edan, por lo tanto, no los puedo volver a crear.\nPrimer problema.... c\u00f3mo eliminar los usuarios hu\u00e9rfanos..... en sql server 2000 lo ten\u00eda bastante por la mano, pero en 2008 no... as\u00ed que \"googleando\" bastante, encontr\u00e9 la soluci\u00f3n <a href=\"https:\/\/www.databasejournal.com\/features\/mssql\/article.php\/1578941\/Removing-Orphan-Users-from-All-databases-on-SQL-Server.htm\" title=\"Remover usuarios hu\u00e9rfanos SQL\">aqu\u00ed<\/a>.\nEl proceso es sencillo&#8230;.. abrimos el analizador de consultas y sobre las BBDD ejecutamos:\n<code>\nselect u.name from master..syslogins l right join\n    sysusers u on l.sid = u.sid\n    where l.sid is null and issqlrole <> 1 and isapprole <> 1\n    and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'\n    and u.name <> 'system_function_schema')\n<\/code>\nY ahora, para eliminar a los usuarios&#8230;. en la misma pantalla y por BBDD, ejecutamos:\n<code>\nexec sp_revokedbaccess 'usuario_a_borrar'\n<\/code>\nY con esto, ya tenemos solucionado el problema]]>\t\t<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\t\t\t\t<![CDATA[]]>\t\t<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[176,197],"class_list":["post-1385","post","type-post","status-publish","format-standard","hentry","category-varios","tag-sql-server-2008","tag-windows"],"_links":{"self":[{"href":"https:\/\/diablo.craem.net\/index.php?rest_route=\/wp\/v2\/posts\/1385","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/diablo.craem.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/diablo.craem.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1385"}],"version-history":[{"count":0,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=\/wp\/v2\/posts\/1385\/revisions"}],"wp:attachment":[{"href":"https:\/\/diablo.craem.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1385"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1385"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1385"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}