Orpahned users? Use this and clean it all up! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Orpahned users? Use this and clean it all up!

Ever restore (a) database(s) which have users that are on the server you are restoring to? When you look at the users, they dont have the database assigned to them, but they are in the security area of the database you restored. This is a issue where the userids do not synch up from one server to another. If you use my script it will go through the entire server and fix these issues. This usually happens when doing database restores from prod to dev. If there are no orphans, then the script wont return a thing! set nocount on
go DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)
DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name not in (‘master’, ‘msdb’, ‘model’, ‘tempdb’,’AdventureWorks’,’AdventureWorksDW’)
OPEN Curse FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = ‘USE ‘+ @databasename + ‘
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
IF exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command=” sp_change_users_login auto_fix, ” + @usrname
print @command
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs’
EXEC dbo.sp_executesql @strSQL
fetch next from Curse into @databasename
end
close Curse
deallocate Curse
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |