Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/glenn/wp-config.php on line 94
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":825,"date":"2013-08-23T12:10:18","date_gmt":"2013-08-23T19:10:18","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=825"},"modified":"2013-10-22T14:16:39","modified_gmt":"2013-10-22T21:16:39","slug":"how-to-avoid-orphaned-database-users-with-sql-server-authentication","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/","title":{"rendered":"How To Avoid Orphaned Database Users with SQL Server Authentication"},"content":{"rendered":"One common issue that database administrators often run into is the old, familiar \u201corphaned\u201d user problem. This happens when you use SQL Server Authentication to create a SQL Server login on your database server. When you do this, SQL Server generates a unique SID for that SQL Server login. After you create the SQL Server login, you typically create a database user in a user database on that server instance, and associate the database user with that SQL Server login.<\/p>\n
This works fine until you try to restore that user database to another SQL Server instance. If you previously created a SQL Server login with the same UserID on the new server, the SID for that SQL Server login will not match the database user in the user database that you have restored (from the other database instance). Hence the term \u201corphaned\u201d user.\u00a0 This is an especially big issue if you are using database mirroring, since your database users will be orphans when you failover from one instance to the other instance. It is also an issue with log shipping, and it often comes up when you migrate from an old database server to a new database server.<\/p>\n
There are several ways to fix this, but the best thing (outside of just using Windows Authentication and avoiding the problem), is to create the new server login using the same SID as on the original server. Just like you see below:<\/p>\n
-- Get Sids for all SQL Server logins on the old server instance<\/span>\r\nSELECT<\/span> name, [sid] \r\nFROM<\/span> sys.server_principals\r\nWHERE<\/span> [type] = 's'<\/span>; \r\n\r\n-- Create new SQL Login on new server instance<\/span>\r\nIF<\/span> EXISTS<\/span> (SELECT<\/span> * FROM<\/span> sys.server_principals WHERE<\/span> name = N'SQLAppUser'<\/span>)\r\n DROP<\/span> LOGIN SQLAppUser;\r\nGO<\/span>\r\n\r\n-- Use the sid from the old server instance <\/span>\r\nCREATE<\/span> LOGIN SQLAppUser WITH<\/span> PASSWORD = N'YourStrongPassword#'<\/span>, sid = 0x2F5B769F543973419BCEF78DE9FC1A64,\r\nDEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF<\/span>, CHECK_POLICY=OFF<\/span>;\r\nGO<\/span><\/pre>\n