Fixing Orphaned Logins

Find Orphaned Logins

sp_change_users_login @Action=‘Report’
GO

Fix an Individual Login

--Replace Login_name with users login
sp_change_users_login 'auto_fix', 'login_name'
go

Fix all logins for a database

USE [database]
go
-- fix Users
set nocount on
declare @v_dbuser varchar(255)
declare @sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name --COLLATE Latin1_General_CI_AS
      open c1
      fetch c1 into @v_dbuser
      while (@@FETCH_STATUS <> -1)
      BEGIN
      print 'Fixing User ' + @v_dbuser
      set @sql = 'sp_change_users_login ''auto_fix'', [' + @v_dbuser + ']'
      exec sp_executesql @sql
      fetch c1 into @v_dbuser
      END
CLOSE C1
DEALLOCATE C1

Fix all logins on a SQL Server Instance

DECLARE @DBName varchar(255)

DECLARE DBCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE database_id > 4

OPEN DBCursor

FETCH NEXT FROM DBCursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN 

-- fix Users
set nocount on
declare @v_dbuser varchar(255)
declare @sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name COLLATE Latin1_General_CI_AS
      open c1
      fetch c1 into @v_dbuser
      while (@@FETCH_STATUS <> -1)
      BEGIN
      print 'Fixing User ' + @v_dbuser
      set @sql = 'sp_change_users_login ''auto_fix'', [' + @v_dbuser + ']'
      exec sp_executesql @sql
      fetch c1 into @v_dbuser
      END
CLOSE C1
DEALLOCATE C1


FETCH NEXT FROM DBCursor INTO @DBName
END

CLOSE DBCursor

DEALLOCATE DBCursor

return
GO

Troubleshooting

Cannot resolve the collation conflict between "Latin1_General_CI_AS" 
and "Latin1_General_CI_AS_KS_WS" in the equal to operation. 
[SQLSTATE 42000] (Error 468).  The step failed.

To get round the problem of different collations between master db and user db, i added the COLLATE syntax to match up the collations.

select a.name from sysusers a, master..syslogins b
where a.name = b.name COLLATE Latin1_General_CI_AS
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: