--Sql Server 2014 Express Edition
--Batches are separated by 'go'
 
 
 
SELECT @@version AS 'sql server version';
 
IF OBJECT_ID('dbo.employee', 'U') IS NOT NULL
    DROP TABLE dbo.employee;
 
CREATE TABLE [employee] (
    [EmployeeID] BIGINT IDENTITY(1,1),
    [Name] nvarchar(1024) NOT NULL,
    [Pass] CHAR(32) NOT NULL,
    CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY ([EmployeeID]),
    CONSTRAINT UK_Employee_Name UNIQUE NONCLUSTERED ([Name])
);
 
CREATE TABLE [access] (
    [AccessID] BIGINT IDENTITY(1,1),
    [EmployeeID] BIGINT NOT NULL,
    [Permission] nvarchar(2048) NOT NULL,
    [GrantPerm] bit NOT NULL DEFAULT 0,
    CONSTRAINT PK_Access_AccessID PRIMARY KEY (AccessID),
    CONSTRAINT FK_Access_EmployeeID FOREIGN KEY (EmployeeID)
    REFERENCES dbo.employee (EmployeeID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
 
INSERT INTO [employee] ( [Name] , [Pass] ) VALUES ( 'TestUser' , CONVERT(VARCHAR(32),HashBytes('MD5',CONCAT('TestUser','secret')),2) );
 
SELECT * FROM [employee];
 
INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , '*', 0);
INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , 'member:profile:own', 1);
INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , 'member:store:comments:*', 1);
INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , 'member:update:comments:own', 1);
 
SELECT access.*, employee.[Name] FROM [access]
LEFT JOIN [employee] ON access.[EmployeeID] = employee.[EmployeeID];