Pages

Monday, March 10, 2014

Command to map an orphaned user for SQL Server Login id and Database user id


After restoring the SQL Server Database from one server to another server, SQL Server Login id and Database user id does match, so it look like a orphaned user, to fix the issue then follow the commands


-Script to view difference in SID

USE MASTER
GO
SELECT name as SQLServerLogIn,SID as SQLServerLogInSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO

USE AdventureWorks
GO
SELECT name DataBaseUserID,SID as DatabaseUserSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

--Command to generate list of orphaned users

USE adventureWorks
GO

sp_change_users_login @Action='Report'
GO


--Command to map an orphaned user

USE AdventureWorks
GO

sp_change_users_login @Action='update_one',
@UserNamePattern='TestUser1',
@LoginName='TestUser1'
GO

No comments:

Post a Comment