Category Archives: Notes

All Articles

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.

Enabling Filestream to store compress documents

Leave a reply

Problem: If you are in the process of enabling FILESTREAM on volume and you also want to ensure that documents will be stored in the compressed form but during that time you notice that volume F:\ drive is not formatted. You need to make sure that the volume will be able to store documents in a compressed form.  You can fix the issue by using below command.

Command to format F:\
format F: /FS:NTFS /V:MYBLOBContainer/A:4096 /C

The above command will format the volume, convert it to NTFS and then label it as MYBLOBContainer. It will also set the NTFS cluster size to 4096. It is important to note that the cluster size must be 4096 or smaller for compression to be enabled for a volume. And at last, the /c parameter will compress the volume. When you are storing the FILESTREAM data, you can store it on a compressed or a non-compressed volume. The data in FILESTREAM is actually stored on the file system. There are a number of benefits to storing the data on the file system. For instance, you will be able to compress data with the NTFS compression capabilities. When the data is stored, it is in compressed form and when the data is retrieved, it is decompressed.