Sunday, March 6, 2011

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