if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CRM_LoginUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CRM_LoginUser]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CRM_LoginUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CRM_LoginUser] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[LoginName] [varchar] (40) NULL ,--用户名
[Password] [varchar] (500) NULL ,--密码
[IsAdmin] [bit] NOT NULL ,--是否管理员,1为是,0为否
[InnerObject] [varchar] (20) NULL ,
[InnerObjectTypeID] [varchar] (30) NULL ,
[OuterObject] [varchar] (20) NULL ,
[OuterObjectTypeID] [varchar] (30) NULL ,
[LimitString] [varchar] (500) NULL ,
[LatestLogonTime] [datetime] NULL ,--最后登陆时间
[TempletID] [int] NOT NULL ,--角色模板
[TempletName] [varchar] (50) NOT NULL ,
[LogonNumber] [int] NOT NULL ,
[TotalOnLineTime] [int] NOT NULL ,
[Description] [varchar] (100) NOT NULL ,
[CanViewEmpStyleID] [varchar] (200) NOT NULL ,
[LastEditPassWordTime] [datetime] NOT NULL ,--最后一次修改密码
[IsLockLogin] [bit] NOT NULL --是否锁住登陆
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[CRM_LoginUser] WITH NOCHECK ADD
CONSTRAINT [DF_CRM_LoginUser_IsAdmin] DEFAULT (0) FOR [IsAdmin],
CONSTRAINT [DF__CRM_Login__Templ__0A888742] DEFAULT (0) FOR [TempletID],
CONSTRAINT [DF__CRM_Login__Templ__0B7CAB7B] DEFAULT ('') FOR [TempletName],
CONSTRAINT [DF__CRM_Login__Logon__0C70CFB4] DEFAULT (0) FOR [LogonNumber],
CONSTRAINT [DF__CRM_Login__Total__0D64F3ED] DEFAULT (0) FOR [TotalOnLineTime],
CONSTRAINT [DF__CRM_Login__Descr__0E591826] DEFAULT ('') FOR [Description],
CONSTRAINT [DF__CRM_Login__CanVi__2685A772] DEFAULT ('') FOR [CanViewEmpStyleID],
CONSTRAINT [DF__CRM_Login__LastE__12549193] DEFAULT (getdate()) FOR [LastEditPassWordTime],
CONSTRAINT [DF__CRM_Login__IsLoc__1348B5CC] DEFAULT ('0') FOR [IsLockLogin]
GO
ALTER TABLE dbo.CRM_LoginUser ADD
EmployeeID char(10) NULL--用于记录zy_sys2_ygbm,zy_yp1_proxy,zy_yp1_manager的union表关联zy_view_operator
--已经记用户
select b.LoginName,b.[Password],b.IsAdmin,b.TempletID,b.[Description],b.EmployeeID,a.dm,a.dmmc,
(case left(a.dm,1)
when 'H' then '代理商'
when 'M' then '招商经理'
else '公司员工' end )
as type
from zy_view_operator a,crm_loginuser b where a.dm=b.employeeid
权限管理的设置应以角色为基础,用户并不直接关联角色,而是以角色为权限为模板,加以引用到用户,这样减少权限设置,这样设计又可对用户进行个性化的权限设置,灵活性更大
