SQL Server Performance

Orpahned users? Use this and clean it all up!

Discussion in 'Michael Berry' started by MichaelB, May 23, 2008.

  1. MichaelB Member

    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

Share This Page