Auto Fix Orphaned Users.

When you move your database to any higher version, Logins and Users are not sync.

This type of problem comes while the migraion, SQL calls that all users as Orphaned Users and we have to link it manually.

we have the script bellow to link it manually.

USE ADVENTUREWORKS2012
GO
DECLARE @Users TABLE
    (
         Id    INT IDENTITY(1, 1),
         Users VARCHAR(250)
    )
DECLARE @Rows     INT,
        @Username VARCHAR(250)

INSERT INTO @Users

SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN ( 'guest', 'sa', 'dbo', 'public',
                           'sys', 'INFORMATION_SCHEMA' )
SET @Rows = @@ROWCOUNT
WHILE ( @Rows > 0 )
  BEGIN
      SELECT @Username = Users
      FROM   @Users
      WHERE  Id = @Rows

      EXEC Sp_change_users_login 'Auto_Fix', @Username, NULL, @Username
	  SELECT @Username
	  SET @Rows = @Rows - 1
  END

Thanks & Regards,
Nirav Gajjar

Partition Table in SQL Server

To create table partition your database must need Multple file group database to apply Table partition.

Step1 : Create Multiple Filegroup Database.

CREATE DATABASE MULTIFILEGROUP
ON PRIMARY
(NAME = N'MFGTest_1', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_1.mdf', SIZE = 5124KB, FILEGROWTH = 10% ),
FILEGROUP [SECONDARY]
(NAME = N'MFGTest_2', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_2.ndf', SIZE = 1024KB, FILEGROWTH = 10% )
LOG ON
( NAME = N'MFGTest_Log', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_Log.ldf', SIZE = 1024KB, FILEGROWTH = 10% )

Step2 : Create Partition Function to define partition block .

here, i have created two partition block

See Values(10) means first partition contains 1-10 and second partition contains > 10.

you can create number of partition block same as number of file group in your database.

you can create multiple block as below.
VALUES(10,20,30,40)– it will create 5 blocks 1-10, 11-20, 21-30, 31-40, >40

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION (INT) AS RANGE RIGHT FOR
VALUES(10)

Step3 : Create partition Schema.

--Create Parition Schema
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION AS PARTITION
FN_PARTITION_TBLPARTITION TO([PRIMARY], [SECONDARY])

Step4 : Create Parition Table.

CREATE TABLE Tblpartition
  (
     Seq         INT IDENTITY(1, 1) NOT NULL,
     Fisrt_name  VARCHAR(20),
     Last_name   VARCHAR(20),
     Split_value INT NOT NULL
  )
ON SCH_PARTITION_TBLPARTITION(SPLIT_VALUE)

ALTER TABLE Tblpartition
  ADD CONSTRAINT PK_TBLPARTITION PRIMARY KEY (Seq, Split_value)

Step5 : Insert sample data in partition table.

INSERT INTO Tblpartition
SELECT 'Nirav', 'Gajjar', 1
UNION ALL
SELECT 'Smith', 'Mack', 2
UNION ALL
SELECT 'Jack', 'Samson', 3
UNION ALL
SELECT 'Smith', 'Raka', 10
UNION ALL
SELECT 'Hen', 'Vaith', 11

Step6 : Script to get Paritition Table Information.

SELECT
T.Name             AS Table_name,
Ps.Name            AS Partition_scheme,
Pf.Name            AS Partition_function,
CASE
WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
ELSE 'LEFT'
END                AS Range_type,
P.Partition_number AS Partition_number,
P.Rows             AS Numberofrows
FROM
SYS.Tables T
JOIN   SYS.Indexes I
ON T.Object_id = I.Object_id
JOIN   SYS.Partition_schemes Ps
ON I.Data_space_id = Ps.Data_space_id
JOIN   SYS.Partition_functions Pf
ON Ps.Function_id = Pf.Function_id
JOIN   SYS.Partitions P
ON I.Object_id = P.Object_id
AND I.Index_id = P.Index_id
WHERE
I.Index_id < 2 --Filter for Clustered Index
ORDER  BY
P.Partition_number

Regards,
Nirav Gajjar