Category Archives: SQL Server

Script to fix and list out Orphan Users on all the databases on server

3 Replies


USE MASTER
GO
SET NoCount ON
DECLARE @VarDbId INT,
@SQL nvarchar(4000),
@VDBName nvarchar(260),
@OUCounter INT,
@Max_OUCounter INT
SELECT @VarDbId=4,
@SQL =''
CREATE TABLE #OrphaneUsers
(
ID INT IDENTITY (1,1) NOT NULL,
DBName VARCHAR(125) NULL ,
UserName sysname NULL ,
UserSID VARBINARY(85) NULL ,
LoginExists bit NULL
)
WHILE EXISTS
(SELECT database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
)
BEGIN
SELECT TOP 1
@SQL ='Create table #OrphaneUser

(UserName sysname null,

UserSID varbinary(85) null )

insert into #OrphaneUser exec ' + name+ '.dbo.sp_change_users_login ''report''

insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ name+''' as[dbname], UserName, UserSID,0 from #OrphaneUser

drop Table #OrphaneUser',
@VDBName=name
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
ORDER BY database_id
EXEC SP_Executesql @SQL
SELECT TOP 1
@VarDbId=database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
END
UPDATE #OrphaneUsers
SET LoginExists=1
FROM #OrphaneUsers
JOIN syslogins
ON #OrphaneUsers.UserName=syslogins.NAME
SELECT @OUCounter =0,
@Max_OUCounter =COUNT(0)
FROM #OrphaneUsers
WHERE LoginExists=1
WHILE EXISTS
(SELECT TOP 1
id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
)
BEGIN
SELECT TOP 1
@OUCounter=id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Auto_Fix'', '''+UserName+''', NULL, '''+UserName+''''
FROM #OrphaneUsers
WHERE LoginExists=1
AND id =@OUCounter
EXEC SP_Executesql @SQL
PRINT @SQL
END
SELECT *
FROM #OrphaneUsers
DROP TABLE #OrphaneUsers

RAID Levels

1 Reply

RAID Levels
RAID 5:-  RAID 5 will provide good performance throughput and prevent data loss if there is a disk failure. For the database which has high read/write activity, RAID 5 will be a better choice when compared to RAID 0 and RAID 1 in terms of data availability and data protection. For an ideal SQL Server configuration, you should have a combination of RAID 1 and RAID 5. You should mirror the operating system using RAID 1, and place transaction logs on a RAID 1 that is separate from the RAID 1 that hosts the operating system. SQL Server writes data to the transaction logs and maintains serial information of all modifications that occurred in a SQL database. The transaction log files can be used for rollback and roll forward of information from a SQL Server database. The SQL Server files and filegroups should be placed on a RAID 5, because you get best performance throughput by placing database files on RAID 5 disk array.

RAID 10 RAID 10 is a better choice than RAID 5, but RAID 10 would be more expensive than RAID 5. RAID 10 is a combination of RAID 0+1, which is known as striping with mirroring. You should first mirror disks and then create a stripe set of mirrored disks. This provides high fault tolerance for data and excellent throughput performance.

RAID 1 RAID 1 is known as disk mirroring. You need a minimum of two disks to form a RAID 1 array. One primary disk is used for read/write operations and the data is replicated to the second disk. This RAID level offers better read performance but slower write performance.

RAID 0 RAID 0 is known as disk striping. This RAID level stripes data across disks in the array, offering better throughput on the read/write operations. However, there is no data protection offered in this RAID level. If one disk fails, the data stored on the disk will be lost.