{"id":348,"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","status":"publish","type":"post","link":"https:\/\/diablo.craem.net\/?p=348","title":{"rendered":"Eliminar usuarios &quot;hu\u00e9rfanos&quot; en sql server 2008"},"content":{"rendered":"<p>Estos d\u00ed\u00adas, he tenido que migrar un aplicativo en un cliente y ello conlleva la migraci\u00f3n del sql server 2000 a sql server 2008.<br \/>\nno cabe decir que, me he tenido que buscar la vida \u00abbastante\u00bb para migrar el sql&#8230;.. el proveedor me d\u00e1 el CD de instalaci\u00f3n y un \u00abb\u00fascate la vida\u00bb para hacerlo&#8230;.. y la expresi\u00f3n: \u00bb si tienes problemas con el SQL, te enviamos un t\u00e9cnico\u00bb.<br \/>\nen fin, ten\u00ed\u00ada que hacerlo s\u00ed\u00ad o s\u00ed\u00ad.<br \/>\n1\u00ba) Instal\u00e9 el sql server en otro servidor<br \/>\n2\u00ba) Hice copia de seguridad de las BBDD de SQL 2000<br \/>\n3\u00ba) Recuper\u00e9 las copias<br \/>\nAhora llega el problema&#8230; ten\u00ed\u00ada que crear los usuarios en el SQL server 2008 y problema&#8230;. los usuarios ya exist\u00ed\u00adan, por lo tanto, no los puedo volver a crear.<br \/>\nPrimer problema&#8230;. c\u00f3mo eliminar los usuarios hu\u00e9rfanos&#8230;.. en sql server 2000 lo ten\u00ed\u00ada bastante por la mano, pero en 2008 no&#8230; as\u00ed\u00ad que \u00abgoogleando\u00bb 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\u00ad<\/a>.<br \/>\nEl proceso es sencillo&#8230;.. abrimos el analizador de consultas y sobre las BBDD ejecutamos:<br \/>\n<code><br \/>\nselect u.name from master..syslogins l right join<br \/>\n    sysusers u on l.sid = u.sid<br \/>\n    where l.sid is null and issqlrole <> 1 and isapprole <> 1<br \/>\n    and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'<br \/>\n    and u.name <> 'system_function_schema')<br \/>\n<\/code><br \/>\nY ahora, para eliminar a los usuarios&#8230;. en la misma pantalla y por BBDD, ejecutamos:<br \/>\n<code><br \/>\nexec sp_revokedbaccess 'usuario_a_borrar'<br \/>\n<\/code><br \/>\nY con esto, ya tenemos solucionado el problema<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Estos d\u00ed\u00adas, he tenido que migrar un aplicativo en un cliente y ello conlleva la migraci\u00f3n del sql server 2000 a sql server 2008. no cabe decir que, me he tenido que buscar la vida \u00abbastante\u00bb para migrar el sql&#8230;.. el proveedor me d\u00e1 el CD de instalaci\u00f3n y un \u00abb\u00fascate la vida\u00bb para hacerlo&#8230;.. [&hellip;]<\/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-348","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\/348","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=348"}],"version-history":[{"count":0,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=\/wp\/v2\/posts\/348\/revisions"}],"wp:attachment":[{"href":"https:\/\/diablo.craem.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=348"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/diablo.craem.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}