work with sqlserver

sqlserver

1.createdabase USE master; CREATE DATABASE exp;

2.create user use exp; CREATE LOGIN exp_guest WITH PASSWord=’xxxx’;

CREATE LOGIN exp_root WITH PASSWORD=’xxxx’;

CREATE LOGIN exp_admin WITH PASSWORD=’xxxx’;

CREATE USER exp_guest FOR LOGIN exp_guest; GRANT CREATE TABLE TO exp_guest; GRANT CREATE VIEW TO exp_guest; GRANT SHOWPLAN TO exp_guest;

CREATE USER exp_root FOR LOGIN exp_root; GRANT CREATE TABLE TO exp_root; GRANT CREATE VIEW TO exp_root; GRANT SHOWPLAN TO exp_root;

CREATE USER exp_admin FOR LOGIN exp_admin;

3.create schema

CREATE SCHEMA EXP_GUEST AUTHORIZATION exp_guest; CREATE SCHEMA EXP_ROOT AUTHORIZATION exp_root;

4.set default schema

ALTER USER exp_guest WITH DEFAULT_SCHEMA = EXP_GUEST;

ALTER USER exp_root WITH DEFAULT_SCHEMA = EXP_ROOT;

ALTER USER exp_admin WITH DEFAULT_SCHEMA = EXP_ROOT;

create table xxx ( EXP01 DATE not null , EXP02 DATE not null ….. , constraint PTIME_MASTER_PKC PRimary key (EXP01) ) ; create view VIEW_xxx as select EXP01 ,EXP02 from xxx ; 6. create index create unique index u_xxx on xxx(EXP01,EXP02);

7.add comment EXECUTE sp_addextendedproperty N’MS_Description’, N’XXX’, N’SCHEMA’, N’exp_guest’, N’TABLE’, N’xxx’, NULL, NULL; EXECUTE sp_addextendedproperty N’MS_Description’, N’current time’, N’SCHEMA’, N’exp_guest’, N’TABLE’, N’xxx’, N’COLUMN’, N’EXP01’; EXECUTE sp_addextendedproperty N’MS_Description’, N’old time’, N’SCHEMA’, N’exp_guest’, N’TABLE’, N’xxx’, N’COLUMN’, N’EXP01’;

create sequence CREATE SEQUENCE seqep1 AS int START WITH 1 INCREMENT BY 1;

getvalue: SELECT NEXT VALUE FOR seqep1;

9.grant permission CREATE ROLE role_admin AUTHORIZATION exp_root; ALTER ROLE role_admin ADD MEMBER exp_admin;

GRANT SELECT ON exp_root.xxx TO role_admin; GRANT INSERT,UPDATE ON exp_root.xxx TO role_admin; GRANT DELETE ON exp_root.xxx TO role_admin;