Task:
A login SID no longer matches a SQL login account because either the user was deleted and returned; the account was recreated, or the service account naming convention changed.
Error Messages Typically Displayed Are:
Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role ‘%s’ already exists in the current database
The database DATABASENAME is not accessible. (Object Explorer)
Cannot open user default database. Login failed.
Login failed for user 'NameOfUser'. (Microsoft SQL Server, Error: 4064)
Using ALTER USER command:
Notes:
- In this example, we are going to delete an old user PersonGone and fix the user JohnDoe in the database MWDB
1. Open Transact-SQL (T-SQL) Window:
2. Enter the following to view the list of IDs requiring attention:
>
USE MWDB;
GO
ALTER USER [MINDWATERINGDomain\JohnDoe] WITH LOGIN = [MINDWATERINGDOMAIN\JohnDoe];
GO
Using sp_change_users_login command:
Notes:
- The sp_change_users_login command used here require the membership in the db_owner fixed database role. Use of the Auto_Fix option also requires the sysadmin fixed server role.
- This command is deprecated and the 2025 documentation says will be removed in a future release. Use ALTER USER instead.
- In this example, we are going to delete an old user PersonGone and fix the user JohnDoe in the database MWDB
1. Open Transact-SQL (T-SQL) Window:
2. Enter the following to view the list of IDs requiring attention:
>
USE MWDB
GO
sp_change_users_login @Action='Report';
GO
3a. Remove old users:
>
USE MWDB
GO
sp_revokedbaccess 'MindwateringDomain\PersonGone';
GO
3b. Fix Mismatched users:
Note:
- The auto_fix option will create login accounts for IDs missing and is not recommended for more sensitive production systems
a. Compare the login SIDS to confirm mismatch:
>
USE MASTER
GO
SELECT name as SQLServerLogIn, SID as SQLServerLogInSID FROM sys.syslogins WHERE [name] = 'JohnDoe'
GO
<view output SID>
USE MWDB
GO
SELECT name as SQLServerLogIn, SID as SQLServerLogInSID FROM sysusers WHERE [name] = 'JohnDoe'
GO
<view output SID, should be different, if not, this will not fix anything - likely an access issue>
b - Alternate 1. Fix user using Auto_Fix:
Note:
- Use only when the name (e.g. JohnDoe) is an exact match:
>
USE MWDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'JohnDoe'
GO
b - Alternate 2. Fix user using Update_One:
>
USE MWDB
GO
sp_change_users_login @Action='Update_One',
@UserNamePattern='JohnDoe',
@LoginName='JohnDoe'
GO
Obsolete - Update of System Tables:
1. Start an SQLQuery window.
WARNING:
- Not allowed SQL 2005 and higher, for good reason
- Do you have a backup and is it verified?
2. Run the following snippet with the SID and name specified:
DECLARE @sysxlogins_sid VARBINARY(85)
SELECT @sysxlogins_sid = sid FROM master.dbo.sysxlogins WHERE name = 'MindwateringDomain\NameOfUser'
UPDATE sysusers SET sid = @sysxlogins_sid WHERE name = 'NameOfUser'
previous page
|