Fixing Orphaned Logins

Find Orphaned Logins:

sp_change_users_login @Action=‘Report’
GO

Fix an Individual Login:

sp_change_users_login ‘auto_fix’, login_name
go

Fix All Logins for a database:

— 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
      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

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: