Fixing Orphaned Users

When ever you refresh a database to new location you need to fix the orphaned users on that database.

Below is the script for fixing orphaned users. If the login exists in that instance, below script will automatically fixes the orphaned users, otherwise will provide you the list of orphaned users information:

Below script need to be run on the refreshed database:

USE [Refreshed database name]

SET NOCOUNT ON

DECLARE @User sysname

DECLARE @NameFound bit

DECLARE orphan_cursor CURSOR FOR

-- The follow select statement was lifted from sp_change_users_login

-- under the area where the REPORT parameter is passed in and then

-- modified.  It's purpose is to identify users in the database that

-- do not match to a corresponding login by sid and then identify

-- whether or not there is a orresponding login by name

        select

                        name as UserName,

                        CASE WHEN suser_sid(name) is not null

                                    THEN 1

                                    ELSE 0

                       END as NameFound

            from sysusers

        where issqluser = 1

            and (sid is not null and sid <> 0x0)

            and suser_sname(sid) is null

        order by name

OPEN orphan_cursor

FETCH NEXT FROM orphan_cursor into @User, @NameFound

WHILE @@FETCH_STATUS = 0

BEGIN

            if @NameFound = 1

            begin

                        select 'Synchronizing user '+@User+' with login '+@User

                        exec sp_change_users_login 'UPDATE_ONE', @User, @User

            end

            else

            begin

                        select 'No matching login found for user '+@User

            end

            FETCH NEXT FROM orphan_cursor into @User, @NameFound

END

CLOSE orphan_cursor

DEALLOCATE orphan_cursor

Customized Update Stats on all active databases

Below is the script for Update Stats, which will extract the database information(Only active databases) and run the Update Stats one by one.

You can create a Stored Procedure in MSDB and schedule a job or dirctely you can run it.

declare @dbname sysname  --holds the name of the database
declare @stmt varchar(64)  --SQL Statement to be run
DECLARE @SQLVer int -- SQL Server primary version


set @dbname=@DB

IF @dbname is not NULL  
begin 
set @stmt='use '+'['+@dbname+']'+'exec sp_updatestats'
exec(@stmt)
return
end

SELECT @SQLVer = SUBSTRING(@@VERSION, CHARINDEX('-',@@VERSION)+1,2)
IF @SQLVer <> 9                      --Condition to check the version of SQL Server
begin
declare dbs cursor for 
select name from master..sysdatabases where 
status & (32 + 64 + 128 + 512 + 1024 + 2048 + 4096 + 32768) = 0  and name NOT IN ('master', 'msdb', 'model','tempdb')/*  To skip the databases which are
                                                                        32 = loading
                                                                        64 = pre recovery
                                                                        128 = recovering
                                                                        256 = not recovered
                                                                        512 = offline
                                                                        1024 = read only
                                                                        2048 = dbo use only
                                                                        4096 = single user
                                                                        32768 = emergency mode   */
end
else
begin
declare dbs cursor for 
select name from sys.databases where 
state  & (1 + 2 + 3 + 4 + 5  + 6 ) = 0 and is_read_only <>1 and   name NOT IN ('master', 'msdb', 'model','tempdb')  /*making use of the catalog view in SQL Server2005
                                                            and the state values indicate as below
                                                            Database state:
                                                            0 = ONLINE
                                                            1 = RESTORING
                                                            2 = RECOVERING
                                                            3 = RECOVERY_PENDING
                                                            4 = SUSPECT
                                                            5 = EMERGENCY
                                                            6 = OFFLINE            */

end

open dbs
fetch next from dbs into @dbname

while @@fetch_status=0
begin
print 'updatestats started on'+ '['+@dbname+']'
set @stmt='use '+'['+@dbname+']'+'exec sp_updatestats'
exec(@stmt)

fetch next from dbs into @dbname
end
close dbs
deallocate dbs

Customised CheckDB script for all databases.

Below is the script for Checkdb, which will extract the database information(Only active databases) and run the checkdb one by one.

You can create a Stored Procedure in MSDB and schedule a job or dirctely you can run it.

declare @dbname sysname  -- this variable holds the name of the database 

DECLARE @SQLVer int -- SQL Server primary version 

 

set @dbname=@DB   

   

IF @dbname is not NULL    

begin   

dbcc checkdb (@dbname)   

return   

end   

 

SELECT @SQLVer = SUBSTRING(@@VERSION, CHARINDEX('-',@@VERSION)+1,2) 

IF @SQLVer <> 9                --Condition to check the version of SQL Server  

begin 

declare dbs cursor for   

select name from master..sysdatabases where   

status & (32 + 64 + 128 + 512 + 1024 + 2048 + 4096 + 32768) = 0  /*  To skip the databases which are  

      32 = loading 

      64 = pre recovery 

      128 = recovering 

      256 = not recovered 

      512 = offline 

      1024 = read only 

      2048 = dbo use only 

      4096 = single user 

      32768 = emergency mode   */ 

 

end  

else 

begin  

declare dbs cursor for   

select name from sys.databases where   

state  & (1 + 2 + 3 + 4 + 5  + 6 ) = 0 and is_read_only <>1    /*making use of the catalog view in SQL Server2005 

     and the state values indicate as below 

     Database state: 

     0 = ONLINE  

     1 = RESTORING 

     2 = RECOVERING 

     3 = RECOVERY_PENDING 

     4 = SUSPECT 

     5 = EMERGENCY 

     6 = OFFLINE            */ 

  

end 

 

declare @str varchar(64) 

open dbs   

fetch next from dbs into @dbname   

   

while @@fetch_status=0   

begin   

set @str='dbcc checkdb (['+@dbname+'])' 

exec(@str) 

--dbcc checkdb (@dbname)   

fetch next from dbs into @dbname   

end   

close dbs   

deallocate dbs