用户权限管理模块【表结构及SQL语句】

最新推荐文章于 2026-04-21 03:05:59 发布

原创最新推荐文章于 2026-04-21 03:05:59 发布 · 1.6w 阅读

23 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql #schema #table #null #menu

ASP.NET 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个用户权限管理系统的设计方案，包括用户、角色、权限等关键信息的管理方式，并详细描述了各个表的字段及其作用。

tb_user：用户帐户信息表
列名	数据类型	允许空	说明
UserName	nvarchar(128)	N	用户ID
Password	nvarchar(128)	N	登录名，用户Email
Email	nvarchar(100)	Y	密码
Question	nvarchar(100)	Y	重置密码的问题
Answer	nvarchar(100)	Y	重置密码的答案
RoleID	int	N	角色
UserGroup	int	N	用户组
CreateTime	datetime	N	帐户创建时间
LastLoginTime	datetime	Y	上一次登录的时间
Status	int	N	用户状态
IsOnline	bit	N	是否在线
IsLimit	bit	N	是否受权限限制，0为受限制

/*20120611 BY FMX用户表*/
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE  [dbo].[tb_User](
	[UserID]        [int] IDENTITY(1,1) NOT NULL,
	[UserName]      [nvarchar](128) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Password]      [nvarchar](128) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Email]         [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
	[Question]      [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
	[Answer]        [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
	[RoleID]        [int] NOT NULL CONSTRAINT [DF_Users_RoleID]  DEFAULT ((0)),
	[UserGroup]     [int] NOT NULL CONSTRAINT [DF_Users_UserGroup]  DEFAULT ((0)),
	[CreateTime]    [datetime] NOT NULL,
	[LastLoginTime] [datetime] NULL,
	[Status]        [int] NOT NULL CONSTRAINT [DF_Users_Status]  DEFAULT ((1)),
	[IsOnline]      [bit] NOT NULL CONSTRAINT [DF_Users_IsOnline]  DEFAULT ((0)),
	[IsLimit]       [bit] NOT NULL CONSTRAINT [DF_Users_IsLimit]  DEFAULT ((0)),
 CONSTRAINT [PK_User_ID] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'UserID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名，用户Email' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'UserName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'Password'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'重置密码的问题' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'Question'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'重置密码的答案' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'Answer'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'RoleID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户组' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'UserGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'帐户创建时间' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'CreateTime'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上一次登录的时间' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'LastLoginTime'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户状态' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'Status'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否在线' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'IsOnline'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否受权限限制，0为受限制' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User', @level2type=N'COLUMN', @level2name=N'IsLimit'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户帐户信息表' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_User'

tb_Role：角色信息表
列名	数据类型	允许空	说明
RoleID	int	N	角色ID
RoleGroupID	int	N	分组ID
RoleName	nvarchar(30)	N	角色名称
RoleDescription	nvarchar(50)	Y	说明
RoleOrder	int	N	排序

/*20120611 BY FMX */
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Role](
	[RoleID]            [int] IDENTITY(1,1) NOT NULL,
	[RoleGroupID]       [int] NOT NULL,
	[RoleName]          [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[RoleDescription]   [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[RoleOrder]         [int] NOT NULL CONSTRAINT [DF_tb_Role_RoleOrder]  DEFAULT ((0)),
 CONSTRAINT [PK_tb_Role_1] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Role', @level2type=N'COLUMN', @level2name=N'RoleID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分组ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Role', @level2type=N'COLUMN', @level2name=N'RoleGroupID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Role', @level2type=N'COLUMN', @level2name=N'RoleName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'说明' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Role', @level2type=N'COLUMN', @level2name=N'RoleDescription'

tb_Action：权限信息表
列名	数据类型	允许空	说明
ActionID	int	N	权限ID
ActionName	nvarchar(30)	N	权限名称
ActionTag	nvarchar(50)	N	权限标识
ActionDescription	nvarchar(50)	Y	说明
ActionOrder	int	N	排序

/*20120611 BY FMX  权限表*/
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Action](
	[ActionID]          [int] IDENTITY(1,1) NOT NULL,
	[ActionName]        [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[ActionTag]         [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[ActionDescription] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[ActionOrder]       [int] NOT NULL CONSTRAINT [DF_ActionDir_ActionOrder]  DEFAULT ((0)),
 CONSTRAINT [PK_tb_Action_1] PRIMARY KEY CLUSTERED 
(
	[ActionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Action', @level2type=N'COLUMN', @level2name=N'ActionID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Action', @level2type=N'COLUMN', @level2name=N'ActionName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Action', @level2type=N'COLUMN', @level2name=N'ActionTag'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'说明' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Action', @level2type=N'COLUMN', @level2name=N'ActionDescription'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Action', @level2type=N'COLUMN', @level2name=N'ActionOrder'

tb_Group：分组信息表
列名	数据类型	允许空	说明
GroupID	int	N	分组ID
GroupName	nvarchar(30)	N	组名称
GroupOrder	int	N	排序
GroupDescription	nvarchar(50)	Y	说明
GroupType	int	N	分组类型用户组0,角色组1

/*20120611 BY FMX 分组表*/
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Group](
	[GroupID]          [int] IDENTITY(1,1) NOT NULL,
	[GroupName]        [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[GroupOrder]       [int] NOT NULL CONSTRAINT [DF_RGP_UserGroup_UserGroupOrder]  DEFAULT ((0)),
	[GroupDescription] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[GroupType]        [int] NOT NULL CONSTRAINT [DF_tb_Group_GroupType]  DEFAULT ((0)),
 CONSTRAINT [PK_tb_Group] PRIMARY KEY CLUSTERED 
(
	[GroupID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分组ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Group', @level2type=N'COLUMN', @level2name=N'GroupID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Group', @level2type=N'COLUMN', @level2name=N'GroupName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Group', @level2type=N'COLUMN', @level2name=N'GroupOrder'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'说明' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Group', @level2type=N'COLUMN', @level2name=N'GroupDescription'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分组类型 用户组0,角色组1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Group', @level2type=N'COLUMN', @level2name=N'GroupType'

tb_UserRole：用户角色信息表
列名	数据类型	允许空	说明
UR_ID	int	N	用户角色ID
UserID	int	N	用户ID
RoleID	int	N	角色ID

/*20120611 BY FMX 用户角色表 */
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_UserRole](
	[UR_ID]  [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[RoleID] [int] NOT NULL,
 CONSTRAINT [PK_tb_UserRole] PRIMARY KEY CLUSTERED 
(
	[UR_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

tb_UserGroup：用户分组信息表
列名	数据类型	允许空	说明
UG_ID	int	N	用户组ID
UG_Name	nvarchar(30)	N	用户分组名称
UG_Order	int	N	用户分组排序
UG_Description	nvarchar(50)	N	用户分组描述
UG_Depth	int	N	用户分组深度
UG_SuperiorID	int	N	用户分组上级
UG_Count	int	N	用户分组下级数

/*20120611 BY FMX 用户分组信息表*/
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_UserGroup](
	[UG_ID]          [int] IDENTITY(1,1) NOT NULL,
	[UG_Name]        [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[UG_Order]       [int] NOT NULL,
	[UG_Description] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[UG_Depth]       [int] NOT NULL CONSTRAINT [DF_tb_UserGroup_UG_Depth]  DEFAULT ((0)),
	[UG_SuperiorID]  [int] NOT NULL CONSTRAINT [DF_tb_UserGroup_UG_SuperiorID]  DEFAULT ((0)),
	[UG_Count]       [int] NOT NULL CONSTRAINT [DF_tb_UserGroup_UG_Count]  DEFAULT ((0)),
 CONSTRAINT [PK_tb_UserGroup] PRIMARY KEY CLUSTERED 
(
	[UG_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户组ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_UserGroup', @level2type=N'COLUMN', @level2name=N'UG_ID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户分组名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_UserGroup', @level2type=N'COLUMN', @level2name=N'UG_Name'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户分组排序' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_UserGroup', @level2type=N'COLUMN', @level2name=N'UG_Order'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户分组描述' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_UserGroup', @level2type=N'COLUMN', @level2name=N'UG_Description'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户分组深度' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_UserGroup', @level2type=N'COLUMN', @level2name=N'UG_Depth'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户分组上级' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_UserGroup', @level2type=N'COLUMN', @level2name=N'UG_SuperiorID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户分组下级数' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_UserGroup', @level2type=N'COLUMN', @level2name=N'UG_Count'

tb_MenuType：菜单类型信息表
列名	数据类型	允许空	说明
MenuTypeID	int	N	菜单分类ID
MenuTypeName	nvarchar(30)	N	菜单类型名称
MenuTypeOrder	int	N	排序
MenuTypeDescription	nvarchar(50)	Y	说明
MenuTypeDepth	int	N	深度
MenuTypeSuperiorID	int	N	上级ID
MenuTypeCount	int	N	下阶个数

/*20120611 BY FMX 目录类型表*/
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_MenuType](
	[MenuTypeID]          [int] IDENTITY(1,1) NOT NULL,
	[MenuTypeName]        [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[MenuTypeOrder]       [int] NOT NULL CONSTRAINT [DF_ModuleGroup_ModuleGroupOrder]  DEFAULT ((0)),
	[MenuTypeDescription] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[MenuTypeDepth]       [int] NOT NULL CONSTRAINT [DF_tb_MenuType_MenuTypeDepth]  DEFAULT ((0)),
	[MenuTypeSuperiorID]  [int] NOT NULL CONSTRAINT [DF_tb_MenuType_MenuTypeSuperiorID]  DEFAULT ((0)),
	[MenuTypeCount]       [int] NOT NULL CONSTRAINT [DF_tb_MenuType_MenuTypeCount]  DEFAULT ((0)),
 CONSTRAINT [PK_tb_MenuType_1] PRIMARY KEY CLUSTERED 
(
	[MenuTypeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块分类ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuType', @level2type=N'COLUMN', @level2name=N'MenuTypeID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块类型名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuType', @level2type=N'COLUMN', @level2name=N'MenuTypeName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuType', @level2type=N'COLUMN', @level2name=N'MenuTypeOrder'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'说明' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuType', @level2type=N'COLUMN', @level2name=N'MenuTypeDescription'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'深度' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuType', @level2type=N'COLUMN', @level2name=N'MenuTypeDepth'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuType', @level2type=N'COLUMN', @level2name=N'MenuTypeSuperiorID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'下级个数' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuType', @level2type=N'COLUMN', @level2name=N'MenuTypeCount'

tb_Menu：菜单信息表
列名	数据类型	允许空	说明
MenuID	int	N	菜单ID
MenuTypeID	int	N	菜单类型
MenuName	nvarchar(30)	N	菜单名称
MenuTag	nvarchar(50)	N	菜单标识
MenuURL	nvarchar(500)	Y	菜单地址
MenuDisabled	bit	N	是否禁用
MenuOrder	int	N	排序
MenuDescription	nvarchar(50)	Y	说明
IsMenu	bit	N	是否显示在导航菜单中

/*20120611 BY FMX 菜单表 */
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Menu](
	[MenuID]          [int] IDENTITY(1,1) NOT NULL,
	[MenuTypeID]      [int] NOT NULL,
	[MenuName]        [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[MenuTag]         [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[MenuURL]         [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
	[MenuDisabled]    [bit] NOT NULL CONSTRAINT [DF_tb_Menu_MenuDisabled]  DEFAULT ((1)),
	[MenuOrder]       [int] NOT NULL CONSTRAINT [DF_tb_Menu_MenuOrder]  DEFAULT ((0)),
	[MenuDescription] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[IsMenu]          [bit] NOT NULL CONSTRAINT [DF_tb_Menu_IsMenu]  DEFAULT ((1)),
 CONSTRAINT [PK_tb_Menu] PRIMARY KEY CLUSTERED 
(
	[MenuID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块类型' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuTypeID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuTag'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块地址' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuURL'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否禁用' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuDisabled'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuOrder'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'说明' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'MenuDescription'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否显示在导航菜单中' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Menu', @level2type=N'COLUMN', @level2name=N'IsMenu'

tb_MenuAction：菜单权限信息表
列名	数据类型	允许空	说明
ID	int	N	菜单权限ID
MenuID	int	N	菜单ID
ActionTag	nvarchar(50)	N	权限标识

/*20120611 BY FMX 模块权限表*/
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_MenuAction](
	[ID]        [int] IDENTITY(1,1) NOT NULL,
	[MenuID]    [int] NOT NULL,
	[ActionTag] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_tb_MenuAction] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块权限ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuAction', @level2type=N'COLUMN', @level2name=N'ID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuAction', @level2type=N'COLUMN', @level2name=N'MenuID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_MenuAction', @level2type=N'COLUMN', @level2name=N'ActionTag'

tb_RoleAction：角色权限信息表
列名	数据类型	允许空	说明
ID	int	N	编号
UserID	int	N	用户ID
RoleID	int	N	角色ID
GroupID	int	N	分组ID
MenuID	int	N	菜单ID
ActionTag	nvarchar(50)	N	权限标识
Flag	bit	N	1为允许，0为不禁止

/*20120611 BY FMX 角色权限表 */
USE [SH_Cloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_RoleAction](
	[ID]         [int] IDENTITY(1,1) NOT NULL,
	[UserID]     [int] NOT NULL CONSTRAINT [DF_tb_RoleAction_UserID]  DEFAULT ((0)),
	[RoleID]     [int] NOT NULL CONSTRAINT [DF_tb_RoleAction_RoleID]  DEFAULT ((0)),
	[GroupID]    [int] NOT NULL CONSTRAINT [DF_tb_RoleAction_GroupID]  DEFAULT ((0)),
	[MenuID]     [int] NOT NULL,
	[ActionTag]  [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Flag]       [bit] NOT NULL CONSTRAINT [DF_tb_RoleAction_Flag]  DEFAULT ((1)),
 CONSTRAINT [PK_tb_RoleAction] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_RoleAction', @level2type=N'COLUMN', @level2name=N'ID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_RoleAction', @level2type=N'COLUMN', @level2name=N'UserID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_RoleAction', @level2type=N'COLUMN', @level2name=N'RoleID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分组ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_RoleAction', @level2type=N'COLUMN', @level2name=N'GroupID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_RoleAction', @level2type=N'COLUMN', @level2name=N'MenuID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_RoleAction', @level2type=N'COLUMN', @level2name=N'ActionTag'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1为允许，0为不禁止' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_RoleAction', @level2type=N'COLUMN', @level2name=N'Flag'