BEGIN TRAN
CREATE TABLE Roles
(
PKey int NOT NULL IDENTITY NOT FOR REPLICATION CONSTRAINT PK_Roles PRIMARY KEY CLUSTERED,
RoleId uniqueidentifier NOT NULL CONSTRAINT UQ_Roles_RoleId UNIQUE NONCLUSTERED (RoleId),
RoleName nvarchar(30) NOT NULL
);
CREATE TABLE Users
(
PKey int NOT NULL IDENTITY NOT FOR REPLICATION CONSTRAINT PK_Users PRIMARY KEY CLUSTERED,
UserId uniqueidentifier NOT NULL CONSTRAINT UQ_Users_UsetId UNIQUE NONCLUSTERED (UserId),
UserName nvarchar(50) NOT NULL,
[Password] nvarchar(15) NOT NULL,
HzzoUserCode nchar(7) NULL,
Active tinyint NOT NULL,
RoleId uniqueidentifier NOT NULL CONSTRAINT FK_Users_Roles FOREIGN KEY(RoleId) REFERENCES Roles(RoleId),
Barcode nvarchar(8) NOT NULL CONSTRAINT DF_Users_Barcode DEFAULT(''),
MBO nvarchar(9) NOT NULL CONSTRAINT DF_Users_MBO DEFAULT(''),
PIN nvarchar(10) NOT NULL CONSTRAINT DF_Users_PIN DEFAULT(''),
OIB nvarchar(11) NOT NULL CONSTRAINT DF_Users_OIB DEFAULT(''),
SmartCardNo nvarchar(20) NOT NULL CONSTRAINT DF_Users_SmartCardNo DEFAULT(''),
LastChange smalldatetime NOT NULL CONSTRAINT DF_Users_LastChange DEFAULT(getdate())
);
CREATE TABLE Permission
(
PKey int NOT NULL IDENTITY NOT FOR REPLICATION CONSTRAINT PK_Permission PRIMARY KEY CLUSTERED,
PermissionId uniqueidentifier NOT NULL CONSTRAINT UQ_Permission_PermissionId UNIQUE NONCLUSTERED (PermissionId),
RoleId uniqueidentifier NOT NULL CONSTRAINT FK_Permission_Roles FOREIGN KEY(RoleId) REFERENCES Roles(RoleId),
ControllerId int NOT NULL,
Permission tinyint NOT NULL,
ControllerDesc nvarchar(20) NOT NULL CONSTRAINT DF_Permission_ControllerDesc DEFAULT('')
);
CREATE TABLE UserGridParameters
(
PKey int NOT NULL IDENTITY NOT FOR REPLICATION CONSTRAINT PK_UserGridParameters PRIMARY KEY CLUSTERED,
UserId uniqueidentifier NOT NULL CONSTRAINT FK_UserGridParameters_Users FOREIGN KEY(UserId) REFERENCES Users(UserId),
GridStripes tinyint NULL,
GridColorStripeOdd int NULL,
GridColorStripeEven int NULL,
GridColorFocus int NULL,
GridColorSelected int NULL,
GridColorFocusAndSelected int NULL,
GridColorTextNormal int NULL,
GridColorTextSelected int NULL
);
insert into Roles (RoleId, RoleName) select RoleId, [Role] from MetaDb.dbo.Roles
insert into Users (UserId, UserName, [Password], HzzoUserCode, Active, RoleId, Barcode, MBO, PIN, OIB, SmartCardNo, LastChange) select UserId, UserName, [Password], HzzoUserCode, Active, RoleId, Barcode, MBO, PIN, OIB, SmartCardNo, LastChange from MetaDb.dbo.Users
insert into Permission (PermissionId, RoleId, ControllerId, Permission, ControllerDesc) select PermissionId, RoleId, ControllerId, Permission, ControllerDesc from MetaDb.dbo.Permission
insert into UserGridParameters (UserId, GridStripes, GridColorStripeOdd, GridColorStripeEven, GridColorFocus, GridColorSelected, GridColorFocusAndSelected, GridColorTextNormal, GridColorTextSelected) select UserId, GridStripes, GridColorStripeOdd, GridColorStripeEven, GridColorFocus, GridColorSelected, GridColorFocusAndSelected, GridColorTextNormal, GridColorTextSelected from MetaDb.dbo.[Parameters];
commit TRAN