SQLCrazyCertified (2/1/2013)

---

DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)

SQLCrazyCertified (2/1/2013)

---

AND suser_sname(sid) IS NULL

Perry Whittle (2/1/2013)

---

This first part declares a cursor checking sql server user accounts in the specified database ensuring the SID is not null and the user is not dbo.



This part compares the database user SID to the server level logins and if its null no login exists.

SQLCrazyCertified (2/8/2013)

---

Hi Perry,

So, basically what it is doing is, it is creating the login using the SID of the user? and mapping it?..

SQLCrazyCertified (2/8/2013)

---

I can understand if it's Windows authentication, but if it's SQL authentication, how does this work?....Please let me know if my understanding is correct?

Thanks,
SueTons.

Richard Fryar (2/8/2013)

---

Often, you find yourself failing over several databases, or several hundred in a real disaster scenario.

This script checks all databases in one go, and avoids use of a cursor.

exec sp_msforeachdb 'use [?];
declare @sql varchar(max)
select @sql = s from (
	select ''use ['' + db_name() + '']; exec sp_change_users_login ''''Update_One'''', '''''' + d.name + '''''', '''''' + d.name + '''''';''
	from sys.database_principals d
	join sys.server_principals p on d.name = p.name
	left join sys.server_principals o on d.sid = o.sid
	where o.name is null
	and d.name not in (''public'')
	for xml path('''')) x(s);
if @sql is not null exec(@sql)
'

Richard Fryar (2/8/2013)

---

This script checks all databases in one go, and avoids use of a cursor.

SQLCrazyCertified (2/8/2013)

---

Is it going through each database that I failover and checking for orphan users and fixing them at the same time?

SueTons.

Richard Fryar (2/8/2013)

---

Ha ha you're right - and i have no issue with an underlying cursor for a one-off task like this, though i know there are some who disagree with me and avoid them at all costs.

I've also seen comments from people who don't like sp_msforeachdb but i find it a real timesaver.

Regarding the original question - I omitted to answer the other part. As well as fixing orphans you also need to make sure that other objects are on the mirror, such as missing logins, agent jobs, linked servers, credentials...

These cannot be left until you failover in case the principal is not available, so you need to keep the instances synchronized regularly. For some objects it can be scripted (logins, jobs) but for others you have to do it manually (you need to know the password to create credentials).