{"id":300,"date":"2011-03-02T09:30:32","date_gmt":"2011-03-01T23:30:32","guid":{"rendered":"http:\/\/jcrawfor74.wordpress.com\/?p=300"},"modified":"2024-06-25T15:05:07","modified_gmt":"2024-06-25T05:05:07","slug":"reconnect-all-sql-logins-after-db-restore","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2011\/03\/02\/reconnect-all-sql-logins-after-db-restore\/","title":{"rendered":"Reconnect all SQL logins after DB restore"},"content":{"rendered":"<div id=\"ntsbl-168241907\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>\n<p>Hi,<\/p>\n\n\n\n<p>Below is a script that I use to reconnect sql logins after a database restore.<\/p>\n\n\n\n<p>It reconnects all users to all databases.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">Use Master\nGO\n\nSet NoCount On\n\nif exists(select * from tempdb..sysobjects where id = object_id('tempdb..#tblDatabases'))\n Drop Table #tblDatabases\n\nif exists(select * from tempdb..sysobjects where id = object_id('tempdb..#tblUsers'))\n Drop Table #tblUsers\n\nDeclare @db nvarchar(max)\nDeclare @usr nvarchar(max)\nDeclare @sql nvarchar(Max)\nDeclare @msg nvarchar(200)\n\nCreate Table #tblDatabases\n(\n DBName nvarchar(4000)\n)\n\nCreate Table #tblUsers\n(\n UserName nvarchar(4000)\n)\n\nInsert Into #tblDatabases\nselect name from master.dbo.sysdatabases \nwhere dbid >= 4 \n      and status in (65544, 65536)\nOrder By name\n\nSelect Top 1 @db = DBname from #tblDatabases\n\nWhile @db is not null\nBegin\n set @msg = Char(13) + 'Processing ' + @db\n RAISERROR(@msg, 0, 0) WITH NOWAIT \n\n set @sql = N'\n Use [' + @db + ']\n\n Declare @usr nvarchar(max)\n set @usr = null\n\n Truncate Table #tblUsers;\n Insert Into #tblUsers SELECT name from sysusers where altuid is null\n                 and name not in (''dbo'', ''guest'', ''sys'', ''INFORMATION_SCHEMA'');\n Select Top 1 @usr = userName from #tblUsers;\n\n While @usr is not null\n Begin\n\n print ''fixing user '' + @usr\n exec sp_change_users_login ''auto_fix'', ''@usr''\n\n Delete from #tblUsers where username = @usr\n Set @usr = null\n Select Top 1 @usr = userName from #tblUsers\n End'\n\n --print @sql\n exec(@sql)\n\n -- main loop\n Delete from #tblDatabases where DBName = @db;\n\n Set @db = null\n Select Top 1 @db = DBname from #tblDatabases\nEnd\n\nDrop Table #tblDatabases\nDrop Table #tblUsers\n\nSet NoCount Off<\/code><\/pre>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>Cheers<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, Below is a script that I use to reconnect sql logins after a database restore. It reconnects all users to all databases. Cheers<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13],"tags":[],"class_list":["post-300","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=300"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/300\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}