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;

1. exp_guest/exp_root login and create table and view

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’;

1. 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;