In short: If you restore a database – which was backed up on a different server – and want to create a server login and map it to your database user, you may get an error message. Reason: the user already exists in your database and cannot not be re-created. So you have to find the orphaned user in your database;<\/p>\n
use YOURDBNAME; <\/em><\/p>\n GO; <\/em><\/p>\n EXEC sp_change_users_login ‚Report‘;<\/em><\/p>\n The result should be something like this:<\/p>\n UserName|UserSID dbo|0x6975A7DA42EE2440BDF292BF4D8D338F<\/em><\/p>\n You can now link the login to the db user or create a new Login with the resulted SID above (tested, and working):<\/p>\n CREATE LOGIN [YourSQLServerLogin] WITH PASSWORD = 0x02003A95B96423C3D260F29BE27C39B139426666AEA07E8B8EBA75FG8F4EABB7ACBEEAF9E5E3510A58735E396309ABF3E9DF047378352B8868A0FBDF0BD8EEFE66CAE6C14963 HASHED, SID = 0x6975A7DA42EE2450BDF222BC4D8D338F, DEFAULT_DATABASE = [master]<\/em><\/p>\n Link an existing Login to the db-user:<\/p>\n EXEC sp_change_users_login ‚update_one‘, ‚db_login1‘, ‚db_login1‘;<\/em><\/p>\n Unfortunatelly ‚dbo‘ is an unallowed argument to the procedure sp_change_users_login … – so i’ve done with creating a new user :-) Afterwards you can manage your Login like before via SQl-Statments and\/ or SQL Server Management Studio. Additional Links:Understanding and dealing with orphaned users in a SQL Server database https:\/\/www.mssqltips.com\/sqlservertip\/1590\/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database\/<\/a> http:\/\/www.codeproject.com\/Articles\/594134\/How-to-Fix-Orphaned-SQL-Users https:\/\/msdn.microsoft.com\/en-us\/library\/ms175475.aspx<\/a><\/p>\n Change the owner of the database:<\/p>\n SQL Server 2008 and earlier:<\/em><\/p>\n SQL Server 2012 and alter:<\/p>\n Documentation on Alter Authorisation can be found here: https:\/\/msdn.microsoft.com\/de-de\/library\/ms187359.aspx<\/p>\n","protected":false},"excerpt":{"rendered":" In short: If you restore a database – which was backed up on a different server – and want to create a server login and map it to your database user, you may get an error message. Reason: the user already exists in your database and cannot not be re-created. So you have to find… Weiterlesen »Microsoft SQL Server – Orphaned users ()<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[37,6],"tags":[],"class_list":["post-910","post","type-post","status-publish","format-standard","hentry","category-sql-it","category-technik"],"_links":{"self":[{"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/posts\/910","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/comments?post=910"}],"version-history":[{"count":9,"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/posts\/910\/revisions"}],"predecessor-version":[{"id":936,"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/posts\/910\/revisions\/936"}],"wp:attachment":[{"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/media?parent=910"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/categories?post=910"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.boettrich.info\/blog\/wp-json\/wp\/v2\/tags?post=910"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}use somedb<\/em>\r\ngo<\/em>\r\nsp_changedbowner [ @loginame = ] 'login'<\/em><\/pre>\n
use master\r\n go\r\n ALTER AUTHORIZATION\r\n ON database::[Test-DB]\r\n TO [someLogin]<\/pre>\n