Office中国论坛/Access中国论坛
标题:
一个用户管理的数据库脚本(完整版)
[打印本页]
作者:
zhuyiwen
时间:
2012-2-14 22:57
标题:
一个用户管理的数据库脚本(完整版)
[attach]48343[/attach]
[attach]48338[/attach]
[attach]48341[/attach]
[attach]48339[/attach]
[attach]48340[/attach]
[attach]48342[/attach]
脚本见下面。
作者:
zhuyiwen
时间:
2012-2-14 22:58
数据库和表
/*************************************************
--- 创建 [Person] 数据库
--- 日期:2011-02-14
**************************************************/
USE [master]
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Person')
DROP DATABASE [Person]
GO
CREATE DATABASE [Person] ON
(
NAME = N'Person',
FILENAME = N'D:\DB\Person\Person.mdf',
SIZE = 1024KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'Person_log',
FILENAME = N'D:\DB\Person\Person_log.ldf',
SIZE = 1024KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
GO
USE [Person]
GO
/*************************************************
--- 创建表
**************************************************/
GO
--------------------------------------------------
--- 表 :USysVersion
--- 用途:用户管理数据库版本
--------------------------------------------------
CREATE TABLE [dbo].[USysVersion](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DBSystem] [varchar](50) NOT NULL,
[Main] [int] NOT NULL,
[Sub] [int] NULL Default(0),
[CreateDate] [datetime] NULL Default (GetDate()),
[Version] AS (convert(varchar,[Main]) + '.' + right(('0000' + convert(varchar,[Sub])),4)),
CONSTRAINT [PK_USysVersion] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'用户管理数据库版本',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'USysVersion'
GO
--------------------------------------------------
--- 表 :USysUsers
--- 用途:存储用户
--------------------------------------------------
CREATE TABLE [dbo].[USysUsers](
[UID] [int] IDENTITY(1000,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [varchar](256) NOT NULL DEFAULT (''),
[IsSystem] [bit] NOT NULL DEFAULT (0),
[Description] [nvarchar](256) NULL,
[Enabled] [bit] NOT NULL DEFAULT (1),
[CreateTime] [datetime] NOT NULL DEFAULT (GetDate()),
CONSTRAINT [PK_USysUsers] PRIMARY KEY CLUSTERED
(
[UID] ASC
),
CONSTRAINT [IX_USysUsers] UNIQUE NONCLUSTERED
(
[UserName] ASC
)
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'用户表',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'USysUsers'
GO
--------------------------------------------------
--- 表 :USysUserExtender
--- 用途:存储用户扩展属性
--- 依赖:dbo.USysUserExtender
--------------------------------------------------
CREATE TABLE [dbo].[USysUserExtender](
[UID] [int] NOT NULL,
[RealName] [nvarchar](50) NULL,
[Gender] [nchar](1) NULL,
[Birthday] [datetime] NULL,
[Email] [nvarchar](256) NULL,
[Company] [nvarchar](50) NULL,
[Post] [nvarchar](10) NULL, /* 职位/岗位/职务 */
[Title] [nvarchar](10) NULL, /* 称谓/头衔/职称 */
[Mobile] [varchar](50) NULL,
[Tel] [varchar](50) NULL,
[Province] [nvarchar](10) NULL,
[City] [nvarchar](10) NULL,
[ZipCode] [varchar](6) NULL,
CONSTRAINT [PK_USysUserExtender] PRIMARY KEY CLUSTERED
(
[UID] ASC
),
CONSTRAINT [FK_USysUserExtender_USysUsers]
FOREIGN KEY([UID]) REFERENCES [dbo].[USysUsers] ([UID])
ON UPDATE CASCADE
ON DELETE CASCADE
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'用户扩展属性表',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'USysUserExtender'
GO
--------------------------------------------------
--- 表 :USysGroups
--- 用途:存储用户组
--------------------------------------------------
CREATE TABLE [dbo].[USysGroups](
[GID] [int] IDENTITY(1000,1) NOT NULL,
[GroupName] [nvarchar](50) NOT NULL,
[IsSystem] [bit] NOT NULL DEFAULT (0),
[Description] [nvarchar](256) NULL,
[Enabled] [bit] NOT NULL DEFAULT (1),
[CreateTime] [datetime] NOT NULL DEFAULT (GetDate()),
CONSTRAINT [PK_USysGroups] PRIMARY KEY CLUSTERED
(
[GID] ASC
),
CONSTRAINT [IX_USysGroups] UNIQUE NONCLUSTERED
(
[GroupName] ASC
)
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'用户组表',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'USysGroups'
GO
--------------------------------------------------
--- 表 :USysGroupUsers
--- 用途:存储用户组的用户
--------------------------------------------------
CREATE TABLE [dbo].[USysGroupUsers](
[GID] [int] NOT NULL,
[UID] [int] NOT NULL,
CONSTRAINT [PK_USysGroupUsers] PRIMARY KEY CLUSTERED
(
[GID] ASC,
[UID] ASC
),
CONSTRAINT [FK_USysGroupUsers_USysGroups] FOREIGN KEY([GID])
REFERENCES [dbo].[USysGroups] ([GID])
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT [FK_USysGroupUsers_USysUsers] FOREIGN KEY([UID])
REFERENCES [dbo].[USysUsers] ([UID])
ON UPDATE CASCADE
ON DELETE CASCADE
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'用户组用户表',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'USysGroupUsers'
GO
复制代码
作者:
zhuyiwen
时间:
2012-2-14 22:59
函数
/*************************************************
--- 创建函数
**************************************************/
GO
--------------------------------------------------
--- 函数:fbUSysGeustIsEnabled
--- 目的:判定是否允许匿名来宾用户
--- 返回:0 - 不允许,1 - 允许
--- 依赖:dbo.USysUsers
--------------------------------------------------
CREATE FUNCTION [dbo].[fbUSysGeustIsEnabled]()
RETURNS bit
AS
BEGIN
DECLARE @ret bit
-- 从[dbo].[USysUsers]表中获取 Guest 用户启用
-- 作为系统是否允许匿名
SELECT @ret = [Enabled] FROM [dbo].[USysUsers] WHERE [UID] = 0
IF @ret IS NULL
SET @ret = 0
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'判定是否允许匿名来宾用户,0 - 不允许,1 - 允许',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'FUNCTION',
@level1name=N'fbUSysGeustIsEnabled'
GO
--------------------------------------------------
--- 函数:_ftUSysUsers
--- 用途:获取用户列表(内部调用)
--- 依赖:dbo.USysUsers/dbo.USysUserExtender
--------------------------------------------------
CREATE FUNCTION [dbo].[_ftUSysUsers]
(
@System bit = NULL,
@Enabled bit = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT
u.UID,
u.UserName,
u.Password,
u.IsSystem,
u.Description,
u.CreateTime,
e.RealName,
e.Gender,
e.Birthday,
e.Email,
e.Company,
e.Post,
e.Title,
e.Mobile,
e.Tel,
e.Province,
e.City,
e.ZipCode
FROM
dbo.USysUsers AS u LEFT OUTER JOIN
dbo.USysUserExtender AS e ON u.UID = e.UID
WHERE
((u.Enabled = @Enabled) OR (@Enabled IS NULL)) AND
((u.IsSystem = @System) OR (@System IS NULL))
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'获取用户列表(内部调用)',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'FUNCTION',
@level1name=N'_ftUSysUsers'
GO
--------------------------------------------------
--- 函数:_ftUSysGroups
--- 用途:获取用户组列表(内部调用)
--- 依赖:dbo.USysGroups
--------------------------------------------------
CREATE FUNCTION [dbo].[_ftUSysGroups]
(
@System bit = NULL,
@Enabled bit = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT [GID]
,[GroupName]
,[IsSystem]
,[Description]
,[Enabled]
,[CreateTime]
FROM [dbo].[USysGroups]
WHERE (([Enabled] = @Enabled) OR (@Enabled IS NULL)) AND
(([IsSystem] = @System) OR (@System IS NULL))
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'获取用户组列表(内部调用)',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'FUNCTION',
@level1name=N'_ftUSysGroups'
GO
--------------------------------------------------
--- 函数:ftGetUserGroups
--- 用途:根据用户 ID 获取共所在的用户组列表
--- 参数:@UID
--- 依赖:dbo.USysUsers/dbo.USysGroups/dbo.USysGroupUsers
--------------------------------------------------
CREATE FUNCTION [dbo].[ftGetUserGroups](@UID int)
RETURNS TABLE
AS
RETURN
(
SELECT g.GID, g.GroupName, g.Description, g.IsSystem
FROM dbo.USysUsers u INNER JOIN
dbo.USysGroupUsers gu ON u.UID = gu.UID INNER JOIN
dbo.USysGroups g ON gu.GID = g.GID
WHERE (u.UID = @UID) AND (g.Enabled = 1)
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'根据用户 ID 获取其所在的用户组列表',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'FUNCTION',
@level1name=N'ftGetUserGroups'
GO
--------------------------------------------------
--- 函数:ftGetGroupUsers
--- 用途:根据用户组 ID 获取其当前用户列表
--- 参数:@GID
--- 依赖:dbo.USysUsers/dbo.USysGroups/dbo.USysGroupUsers
--------------------------------------------------
CREATE FUNCTION [dbo].[ftGetGroupUsers](@GID int)
RETURNS TABLE
AS
RETURN
(
SELECT u.UID, u.UserName, u.Description, u.IsSystem
FROM dbo.USysGroups g INNER JOIN
dbo.USysGroupUsers gu ON g.GID = gu.GID INNER JOIN
dbo.USysUsers u ON gu.UID = u.UID
WHERE (g.GID = @GID) AND (u.Enabled = 1)
)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'根据用户组 ID 获取其当前用户列表',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'FUNCTION',
@level1name=N'ftGetGroupUsers'
GO
复制代码
作者:
zhuyiwen
时间:
2012-2-14 23:02
过程 (一)
/*************************************************
--- 创建过程
**************************************************/
GO
--------------------------------------------------
--- 过程:_paUSysSetUserEnabled
--- 目的:设置用户是否启用(内部调用)
--- 参数:
--- @UID - 用户 ID
--- @Enabled - 0-不允许,1-允许
--- 返回:0 - 不成功
--- 依赖:dbo.USysUsers
--------------------------------------------------
CREATE PROCEDURE [dbo].[_paUSysSetUserEnabled]
(
@UID int,
@Enabled bit
)
AS
BEGIN
IF @Enabled IS NOT NULL
UPDATE [dbo].[USysUsers]
SET [Enabled] = @Enabled
WHERE [UID] = @UID
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'设置用户是否启用(内部调用)',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'_paUSysSetUserEnabled'
GO
--------------------------------------------------
--- 过程:_paUSysSetGroupEnabled
--- 目的:设置用户组是否启用(内部调用)
--- 参数:
--- @GID - 用户组 ID
--- @Enabled - 0-不允许,1-允许
--- 返回:0 - 不成功
--- 依赖:dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[_paUSysSetGroupEnabled]
(
@GID int,
@Enabled bit
)
AS
BEGIN
IF @Enabled IS NOT NULL
UPDATE [dbo].[USysGroups]
SET [Enabled] = @Enabled
WHERE [GID] = @GID
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'设置用户组是否启用(内部调用)',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'_paUSysSetGroupEnabled'
GO
--------------------------------------------------
--- 过程:paGroupAddUser
--- 目的:用户组添加用户
--- 参数:
--- @GID - 用户组 ID
--- @UID - 用户 ID
--- 返回:1 - 成功
--- 0 - 不成功
--- -1 - 用户组不存在
--- -2 - 用户不存在
--- -3 - 用户组已经存在该用户
--- 依赖:dbo.USysGroupUsers/dbo.USysUsers/dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupAddUser](@GID int, @UID int)
AS BEGIN
DECLARE @ret int
SET @ret = 0
IF NOT EXISTS(SELECT [GroupName] FROM [dbo].[USysGroups] WHERE [GID] = @GID)
SET @ret = -1
ELSE
IF NOT EXISTS(SELECT [UserName] FROM [dbo].[USysUsers] WHERE [UID] = @UID)
SET @ret = -2
ELSE
IF NOT EXISTS(SELECT [GID] FROM [dbo].[USysGroupUsers] WHERE ([GID] = @GID) AND ([UID] = @UID))
BEGIN
INSERT INTO [dbo].[USysGroupUsers]
([GID], [UID]) VALUES
(@GID, @UID)
SET @ret = @@ROWCOUNT
END
ELSE
SET @ret = -3
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'向用户组中添加用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupAddUser'
GO
--------------------------------------------------
--- 过程:paGroupRemoveUser
--- 目的:用户组移除用户
--- 参数:
--- @GID - 用户组 ID
--- @UID - 用户 ID
--- 返回:1 - 成功
--- 0 - 不成功
--- -1 - 用户组不存在
--- -4 - 用户组不存在该用户
--- 依赖:dbo.USysGroupUsers/dbo.USysUsers/dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupRemoveUser](@GID int, @UID int)
AS BEGIN
DECLARE @ret int
SET @ret = 0
IF NOT EXISTS(SELECT [GroupName] FROM [dbo].[USysGroups] WHERE [GID] = @GID)
SET @ret = -1
ELSE
IF EXISTS(SELECT [GID] FROM [dbo].[USysGroupUsers] WHERE ([GID] = @GID) AND ([UID] = @UID))
BEGIN
DELETE [dbo].[USysGroupUsers] WHERE ([GID] = @GID) AND ([UID] = @UID)
SET @ret = @@ROWCOUNT
END
ELSE
SET @ret = -4
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'从用户组中移除用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupRemoveUser'
GO
--------------------------------------------------
--- 过程:paUserDisableGuest
--- 目的:停用匿名用户
--- 返回:0 - 不成功
--- 依赖:dbo.USysUsers/dbo._paUSysSetUserEnabled
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserDisableGuest]
AS
BEGIN
DECLARE @return_value int
EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
@UID = 0,
@Enabled = 0
RETURN @return_value
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'停用匿名用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserDisableGuest'
GO
复制代码
作者:
zhuyiwen
时间:
2012-2-14 23:03
过程 (二)
--------------------------------------------------
--- 过程:paUserEnableGuest
--- 目的:启用匿名用户
--- 返回:0 - 不成功
--- 依赖:dbo.USysUsers/dbo._paUSysSetUserEnabled
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserEnableGuest]
AS
BEGIN
DECLARE @return_value int
EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
@UID = 0,
@Enabled = 1
RETURN @return_value
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'启用匿名用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserEnableGuest'
GO
--------------------------------------------------
--- 过程:paUserDisableUser
--- 目的:停用指定用户
--- 返回:0 - 不成功
--- 依赖:dbo.USysUsers/dbo._paUSysSetUserEnabled
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserDisableUser](@UID int)
AS
BEGIN
DECLARE @return_value int
EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
@UID = @UID,
@Enabled = 0
RETURN @return_value
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'停用指定用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserDisableUser'
GO
--------------------------------------------------
--- 过程:paUserEnableUser
--- 目的:启用用户
--- 返回:0 - 不成功
--- 依赖:dbo.USysUsers/dbo._paUSysSetUserEnabled
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserEnableUser](@UID int)
AS
BEGIN
DECLARE @return_value int
EXEC @return_value = [dbo].[_paUSysSetUserEnabled]
@UID = @UID,
@Enabled = 1
RETURN @return_value
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'启用指定用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserEnableUser'
GO
--------------------------------------------------
--- 过程:paUsersAdd
--- 用途:增加新用户
--- 返回:0-不成功
--- 依赖:dbo.USysUsers
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUsersAdd]
(
@UID int OUTPUT, /* 用户ID 输出新增自动产生 */
@UserName nvarchar(50), /* 用户名 */
@Password varchar(256), /* 密码 */
@Description nvarchar(256), /* 描述 */
@IsSystem bit = 0, /* 是否系统用户 0-普通用户,1-系统用户 */
@Enabled bit = 1 /* 是否启用 0-停用,1-启用 */
)
AS
BEGIN
DECLARE @ret int
INSERT INTO [dbo].[USysUsers]
([UserName]
,[Password]
,[IsSystem]
,[Description]
,[Enabled])
VALUES
(@UserName
,@Password
,@IsSystem
,@Description
,@Enabled)
IF @@ROWCOUNT >0
SET @ret = SCOPE_IDENTITY( )
ELSE
SET @ret = 0
SET @UID = @ret
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'增加新用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUsersAdd'
GO
--------------------------------------------------
--- 过程:paUsersDel
--- 用途:删除指定用户
--- 返回:0-不成功
--- 依赖:dbo.USysUsers
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUsersDel]
(
@UID int
)
AS
BEGIN
DELETE [dbo].[USysUsers] WHERE [UID] = @UID
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'删除指定用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUsersDel'
GO
--------------------------------------------------
--- 过程:paUserChangePassword
--- 用途:更改指定用户密码
--- 返回:0-不成功
--- 依赖:dbo.USysUsers
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserChangePassword]
(
@UID int, /* 用户ID */
@Password varchar(256) /* 密码 */
)
AS
BEGIN
UPDATE [dbo].[USysUsers]
SET [Password] = @Password
WHERE [UID] = @UID
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'更改指定用户密码',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserChangePassword'
GO
--------------------------------------------------
--- 过程:paUserChangeDescription
--- 用途:更改指定用户描述
--- 返回:0-不成功
--- 依赖:dbo.USysUsers
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserChangeDescription]
(
@UID int, /* 用户ID */
@Description varchar(256) /* 描述 */
)
AS
BEGIN
UPDATE [dbo].[USysUsers]
SET [Description] = @Description
WHERE [UID] = @UID
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'更改指定用户描述',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserChangeDescription'
GO
--------------------------------------------------
--- 过程:paUserChangeUserName
--- 用途:更改指定用户用户名
--- 返回:0-不成功
--- 依赖:dbo.USysUsers
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserChangeUserName]
(
@UID int, /* 用户ID */
@UserName nvarchar(50) /* 用户名 */
)
AS
BEGIN
UPDATE [dbo].[USysUsers]
SET [UserName] = @UserName
WHERE [UID] = @UID
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'更改指定用户用户名',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserChangeUserName'
GO
复制代码
作者:
zhuyiwen
时间:
2012-2-14 23:04
过程 (三)
--------------------------------------------------
--- 过程:paUserUpdateExtender
--- 用途:更新指定用户的扩展属性
--- 返回:0-不成功
--- 依赖:dbo.USysUserExtender
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserUpdateExtender]
(
@UID int,
@RealName nvarchar(50) = NULL,
@Gender nchar(1) = NULL,
@Birthday datetime = NULL,
@Email nvarchar(256) = NULL,
@Company nvarchar(50) = NULL,
@Post nvarchar(10) = NULL,
@Title nvarchar(10) = NULL,
@Mobile varchar(50) = NULL,
@Tel varchar(50) = NULL,
@Province nvarchar(10) = NULL,
@City nvarchar(10) = NULL,
@ZipCode varchar(6) = NULL
)
AS
BEGIN
IF EXISTS (SELECT [UID] FROM [dbo].[USysUserExtender] WHERE [UID] = @UID)
UPDATE [dbo].[USysUserExtender]
SET [RealName] = @RealName
,[Gender] = @Gender
,[Birthday] = @Birthday
,[Email] = @Email
,[Company] = @Company
,[Post] = @Post
,[Title] = @Title
,[Mobile] = @Mobile
,[Tel] = @Tel
,[Province] = @Province
,[City] = @City
,[ZipCode] = @ZipCode
WHERE [UID] = @UID
ELSE
INSERT INTO [dbo].[USysUserExtender]
([UID],[RealName],[Gender],[Birthday],[Email],[Company],[Post],[Title],[Mobile],[Tel],[Province],[City],[ZipCode])
VALUES
(
@UID,
@RealName,
@Gender,
@Birthday,
@Email,
@Company,
@Post,
@Title,
@Mobile,
@Tel,
@Province,
@City,
@ZipCode
)
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'更新指定用户的扩展属性',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserUpdateExtender'
GO
--------------------------------------------------
--- 过程:paUserSetExtender
--- 用途:设置指定用户的指定扩展属性
--- 返回:0-不成功
--- 依赖:dbo.USysUserExtender
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserSetExtender]
(
@UID int,
@ExtenderName sysname,
@Value sql_variant = NULL
)
AS
BEGIN
DECLARE @ret int
SET @ret = 0
IF NOT EXISTS (SELECT [UID] FROM [dbo].[USysUserExtender] WHERE [UID] = @UID)
INSERT INTO [dbo].[USysUserExtender] ([UID]) VALUES (@UID)
IF @@ERROR = 0
BEGIN
IF UPPER(RTRIM(@ExtenderName)) = UPPER('RealName')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [RealName] = CAST(@Value AS nvarchar(50)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Gender')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Gender] = CAST(@Value AS nchar(1)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Birthday')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Birthday] = CAST(@Value AS datetime) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Email')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Email] = CAST(@Value AS nvarchar(256)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Company')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Company] = CAST(@Value AS nvarchar(50)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Post')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Post] = CAST(@Value AS nvarchar(10)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Title')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Title] = CAST(@Value AS nvarchar(10)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Mobile')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Mobile] = CAST(@Value AS varchar(50)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Tel')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Tel] = CAST(@Value AS varchar(50)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('Province')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [Province] = CAST(@Value AS nvarchar(10)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('City')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [City] = CAST(@Value AS nvarchar(10)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
IF UPPER(RTRIM(@ExtenderName)) = UPPER('ZipCode')
BEGIN
UPDATE [dbo].[USysUserExtender] SET [ZipCode] = CAST(@Value AS varchar(6)) WHERE [UID] = @UID
SET @ret = @ret + @@ROWCOUNT
END
END
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'设置指定用户的指定扩展属性',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paUserSetExtender'
GO
复制代码
作者:
zhuyiwen
时间:
2012-2-14 23:05
过程 (四)
--------------------------------------------------
--- 过程:paGroupsAdd
--- 用途:新增用户组
--- 返回:0-不成功
--- 依赖:dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupsAdd]
(
@GID int OUTPUT, /* 用户组ID 输出新增自动产生 */
@GroupName nvarchar(50), /* 用户组名称 */
@Description nvarchar(256), /* 描述 */
@IsSystem bit = 0, /* 是否系统用户 0-普通用户组,1-系统用户组 */
@Enabled bit = 1 /* 是否启用 0-停用,1-启用 */
)
AS
BEGIN
DECLARE @ret int
INSERT INTO [dbo].[USysGroups]
([GroupName]
,[IsSystem]
,[Description]
,[Enabled])
VALUES
(@GroupName
,@IsSystem
,@Description
,@Enabled)
IF @@ROWCOUNT >0
SET @ret = SCOPE_IDENTITY( )
ELSE
SET @ret = 0
SET @GID = @ret
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'新增用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupsAdd'
GO
--------------------------------------------------
--- 过程:paGroupsDel
--- 用途:删除指定用户组
--- 返回:0-不成功
--- 依赖:dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupsDel]
(
@GID int
)
AS
BEGIN
DELETE [dbo].[USysGroups] WHERE [GID] = @GID
RETURN @@ROWCOUNT
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'删除指定用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupsDel'
GO
--------------------------------------------------
--- 过程:paGroupChangeGroupName
--- 用途:更改指定用户组的名称
--- 返回:0 - 不成功
--- -1 - 用户组名为空
--- -2 - 用户组不存在
--- 依赖:dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupChangeGroupName]
(
@GID int,
@GroupName nvarchar(50)
)
AS
BEGIN
DECLARE @ret int
IF @GroupName IS NULL OR RTRIM(@GroupName) = N''
SET @ret = -1
ELSE
IF EXISTS (SELECT [GID] FROM [dbo].[USysGroups] WHERE [GID] = @GID)
BEGIN
UPDATE [dbo].[USysGroups]
SET [GroupName] = @GroupName
WHERE [GID] = @GID
SET @ret = @@ROWCOUNT
END
ELSE
SET @ret = -2
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'更改指定用户组的名称',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupChangeGroupName'
GO
--------------------------------------------------
--- 过程:paGroupChangeDescription
--- 用途:更改指定用户组的描述
--- 返回:0 - 不成功
--- -2 - 用户组不存在
--- 依赖:dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupChangeDescription]
(
@GID int,
@Description nvarchar(256)
)
AS
BEGIN
DECLARE @ret int
IF EXISTS (SELECT [GID] FROM [dbo].[USysGroups] WHERE [GID] = @GID)
BEGIN
UPDATE [dbo].[USysGroups]
SET [Description] = @Description
WHERE [GID] = @GID
SET @ret = @@ROWCOUNT
END
ELSE
SET @ret = -2
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'更改指定用户组的描述',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupChangeDescription'
GO
--------------------------------------------------
--- 过程:paGroupUpdate
--- 用途:更新指定用户组的属性
--- 返回:0 - 不成功
--- -1 - 用户组名为空
--- -2 - 用户组不存在
--- 依赖:dbo.USysGroups
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupUpdate]
(
@GID int,
@GroupName nvarchar(50),
@Description nvarchar(256) = NULL
)
AS
BEGIN
DECLARE @ret int
IF @GroupName IS NULL OR RTRIM(@GroupName) = N''
SET @ret = -1
ELSE
IF EXISTS (SELECT [GID] FROM [dbo].[USysGroups] WHERE [GID] = @GID)
BEGIN
UPDATE [dbo].[USysGroups]
SET [GroupName] = @GroupName
,[Description]= @Description
WHERE [GID] = @GID
SET @ret = @@ROWCOUNT
END
ELSE
SET @ret = -2
RETURN @ret
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'更新指定用户组的属性',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupUpdate'
GO
--------------------------------------------------
--- 过程:paGroupEnableGroup
--- 目的:启用指定用户组
--- 返回:0 - 不成功
--- 依赖:dbo.USysGroups/dbo._paUSysSetGroupEnabled
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupEnableGroup](@GID int)
AS
BEGIN
DECLARE @return_value int
EXEC @return_value = [dbo].[_paUSysSetGroupEnabled]
@GID = @GID,
@Enabled = 1
RETURN @return_value
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'启用指定用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupEnableGroup'
GO
--------------------------------------------------
--- 过程:paGroupDisableGroup
--- 目的:禁用用指定用户组
--- 返回:0 - 不成功
--- 依赖:dbo.USysGroups/dbo._paUSysSetGroupEnabled
--------------------------------------------------
CREATE PROCEDURE [dbo].[paGroupDisableGroup](@GID int)
AS
BEGIN
DECLARE @return_value int
EXEC @return_value = [dbo].[_paUSysSetGroupEnabled]
@GID = @GID,
@Enabled = 0
RETURN @return_value
END
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'禁用用指定用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'PROCEDURE',
@level1name=N'paGroupDisableGroup'
GO
复制代码
作者:
zhuyiwen
时间:
2012-2-14 23:06
视图
/*************************************************
--- 创建视图
**************************************************/
GO
--------------------------------------------------
--- 视图:vUsersAll
--- 用途:所有用户
--- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersAll]
AS
SELECT * FROM [dbo].[_ftUSysUsers](NULL, NULL)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'所有用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vUsersAll'
GO
--------------------------------------------------
--- 视图:vUsersSystem
--- 用途:所有系统用户
--- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersSystem]
AS
SELECT * FROM [dbo].[_ftUSysUsers](1, NULL)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'所有系统用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vUsersSystem'
GO
--------------------------------------------------
--- 视图:vUsersSystemCurrent
--- 用途:当前系统用户
--- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersSystemCurrent]
AS
SELECT * FROM [dbo].[_ftUSysUsers](1, 1)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'当前系统用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vUsersSystemCurrent'
GO
--------------------------------------------------
--- 视图:vUsersSystemDisabled
--- 用途:当前禁用的系统用户
--- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersSystemDisabled]
AS
SELECT * FROM [dbo].[_ftUSysUsers](1, 0)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'当前禁用的系统用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vUsersSystemDisabled'
GO
--------------------------------------------------
--- 视图:vUsersGeneral
--- 用途:所有一般用户
--- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersGeneral]
AS
SELECT * FROM [dbo].[_ftUSysUsers](0, NULL)
GO
--------------------------------------------------
--- 视图:vUsersGeneralCurrent
--- 用途:当前一般用户
--- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersGeneralCurrent]
AS
SELECT * FROM [dbo].[_ftUSysUsers](0, 1)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'当前一般用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vUsersGeneralCurrent'
GO
--------------------------------------------------
--- 视图:vUsersGeneralDisabled
--- 用途:当前禁用的一般用户
--- 依赖:dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersGeneralDisabled]
AS
SELECT * FROM [dbo].[_ftUSysUsers](0, 0)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'当前禁用的一般用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vUsersGeneralDisabled'
GO
--------------------------------------------------
--- 视图:vGroupsAll
--- 用途:所有用户
--- 依赖:dbo.USysGroups/dbo._ftUSysGroups
--------------------------------------------------
CREATE VIEW [dbo].[vGroupsAll]
AS
SELECT [GID]
,[GroupName]
,[IsSystem]
,[Description]
,[Enabled]
,[CreateTime]
FROM [dbo].[_ftUSysGroups](NULL, NULL)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'返回所有用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vGroupsAll'
GO
--------------------------------------------------
--- 视图:vGroupsCurrent
--- 用途:当前已启用的用户组
--- 依赖:dbo.USysGroups/dbo._ftUSysGroups
--------------------------------------------------
CREATE VIEW [dbo].[vGroupsCurrent]
AS
SELECT [GID]
,[GroupName]
,[IsSystem]
,[Description]
,[Enabled]
,[CreateTime]
FROM [dbo].[_ftUSysGroups](NULL, 1)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'返回当前已启用的用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vGroupsCurrent'
GO
--------------------------------------------------
--- 视图:vGroupsSystemCurrent
--- 用途:当前已启用的系统用户组
--- 依赖:dbo.USysGroups/dbo._ftUSysGroups
--------------------------------------------------
CREATE VIEW [dbo].[vGroupsSystemCurrent]
AS
SELECT [GID]
,[GroupName]
,[IsSystem]
,[Description]
,[Enabled]
,[CreateTime]
FROM [dbo].[_ftUSysGroups](1, 1)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'返回当前已启用的系统用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vGroupsSystemCurrent'
GO
--------------------------------------------------
--- 视图:vGroupsGeneralCurrent
--- 用途:当前已启用的一般用户组
--- 依赖:dbo.USysGroups/dbo._ftUSysGroups
--------------------------------------------------
CREATE VIEW [dbo].[vGroupsGeneralCurrent]
AS
SELECT [GID]
,[GroupName]
,[IsSystem]
,[Description]
,[Enabled]
,[CreateTime]
FROM [dbo].[_ftUSysGroups](0, 1)
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'返回当前已启用的一般用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vGroupsGeneralCurrent'
GO
--------------------------------------------------
--- 视图:vUsersGroupsCurrent
--- 用途:返回所有当前用户的用户组
--- 依赖:dbo.USysUsers
--- dbo.USysGroups
--- dbo.USysGroupUsers
--- dbo._ftUSysUsers
--- dbo._ftUSysGroups
--------------------------------------------------
CREATE VIEW [dbo].[vUsersGroupsCurrent] AS
SELECT u.[UID]
, u.[UserName]
, u.[Password]
, u.[IsSystem]
, u.[Description]
, u.[CreateTime]
, u.[RealName]
, u.[Gender]
, u.[Birthday]
, u.[Email]
, u.[Company]
, u.[Post]
, u.[Title]
, u.[Mobile]
, u.[Tel]
, u.[Province]
, u.[City]
, u.[ZipCode]
, g.[GID]
, g.[GroupName]
, g.[IsSystem] AS g_IsSystem
, g.[Description] AS g_Description
, g.[Enabled] AS g_Enabled
FROM [dbo].[_ftUSysUsers](NULL, 1) AS u
LEFT OUTER JOIN [dbo].[USysGroupUsers] AS ug ON u.[UID] = ug.[UID]
LEFT OUTER JOIN [dbo].[_ftUSysGroups](NULL, 1) AS g ON ug.[GID] = g.[GID]
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'返回所有当前用户的用户组',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vUsersGroupsCurrent'
GO
--------------------------------------------------
--- 视图:vGroupsUsersCurrent
--- 用途:返回所有当前用户组的用户
--- 依赖:dbo.USysUsers
--- dbo.USysGroups
--- dbo.USysGroupUsers
--- dbo._ftUSysUsers
--- dbo._ftUSysGroups
--------------------------------------------------
CREATE VIEW [dbo].[vGroupsUsersCurrent] AS
SELECT g.[GID]
, g.[GroupName]
, g.[IsSystem]
, g.[Description]
, g.[Enabled]
, u.[UID]
, u.[UserName]
, u.[Password]
, u.[IsSystem] AS u_IsSystem
, u.[Description] AS u_Description
, u.[CreateTime]
, u.[RealName]
, u.[Gender]
, u.[Birthday]
, u.[Email]
, u.[Company]
, u.[Post]
, u.[Title]
, u.[Mobile]
, u.[Tel]
, u.[Province]
, u.[City]
, u.[ZipCode]
FROM [dbo].[_ftUSysUsers](NULL, 1) AS u
RIGHT OUTER JOIN [dbo].[USysGroupUsers] AS ug ON u.[UID] = ug.[UID]
RIGHT OUTER JOIN [dbo].[_ftUSysGroups](NULL, 1) AS g ON ug.[GID] = g.[GID]
GO
EXEC sp_addextendedproperty
@name=N'MS_Description',
@value=N'返回所有当前用户组的用户',
@level0type=N'USER',
@level0name=N'dbo',
@level1type=N'VIEW',
@level1name=N'vGroupsUsersCurrent'
GO
复制代码
作者:
zhuyiwen
时间:
2012-2-14 23:06
系统数据
/*************************************************
--- 创建系统数据
**************************************************/
--- 创建版本数据
INSERT INTO [dbo].[USysVersion]
([DBSystem],[Main],[Sub],[CreateDate]) VALUES
('Microsoft SQL Server 8.0.2039',1,0,'2012-01-01 1:00:00')
GO
--- 创建系统帐户
SET IDENTITY_INSERT [dbo].[USysUsers] ON
GO
INSERT [dbo].[USysUsers]
([UID], [UserName], [Password], [IsSystem], [Description], [Enabled], [CreateTime]) VALUES
(0, N'Guest', '', 1, N'匿名来宾用户', 0, '2012-01-01 1:00:00')
INSERT [dbo].[USysUsers]
([UID], [UserName], [Password], [IsSystem], [Description], [Enabled], [CreateTime]) VALUES
(1, N'Administrator','sys', 1, N'系统管理员', 1, '2012-01-01 1:00:00')
GO
SET IDENTITY_INSERT [dbo].[USysUsers] OFF
GO
--- 创建系统用户组
SET IDENTITY_INSERT [dbo].[USysGroups] ON
GO
INSERT [dbo].[USysGroups]
([GID], [GroupName], [IsSystem], [Description], [Enabled], [CreateTime]) VALUES
(0, N'Guests', 1, N'来宾用户组', 1, '2012-01-01 1:00:00')
INSERT [dbo].[USysGroups]
([GID], [GroupName], [IsSystem], [Description], [Enabled],[CreateTime]) VALUES
(1, N'Administrators',1, N'系统管理员', 1, '2012-01-01 1:00:00')
INSERT [dbo].[USysGroups]
([GID], [GroupName], [IsSystem], [Description], [Enabled], [CreateTime]) VALUES
(2, N'Power Users', 1, N'一般用户组', 1, '2012-01-01 1:00:00')
GO
SET IDENTITY_INSERT [dbo].[USysGroups] OFF
GO
/* 添加系统用户组用户 */
EXEC [dbo].[paGroupAddUser] @GID=0, @UID=0
EXEC [dbo].[paGroupAddUser] @GID=1, @UID=1
GO
复制代码
作者:
Grant
时间:
2012-2-14 23:07
很好的脚本,值得借鉴这个不知朱老大在用SQL 05 还是08版本
作者:
zhuyiwen
时间:
2012-2-14 23:09
2000
作者:
ycyhjj2865
时间:
2012-2-14 23:17
不错,真的不错。
作者:
andymark
时间:
2012-2-15 09:36
学习一下
作者:
fnsmydyang
时间:
2012-2-15 10:31
学习收藏,谢谢...
作者:
tmtony
时间:
2012-2-15 11:17
大手笔,谢谢分享!
作者:
zhuyiwen
时间:
2012-2-19 17:34
今天试着用手机上网看网页, 还真是慢, 太不实用了。
作者:
yedaoan
时间:
2012-2-19 20:32
亦文又出大作,顶起!
另外,问下adp培训班还有举办吗?
作者:
zhuyiwen
时间:
2012-2-19 22:39
yedaoan 发表于 2012-2-19 20:32
亦文又出大作,顶起!
另外,问下adp培训班还有举办吗?
没人学,办个屁,呵呵
作者:
yedaoan
时间:
2012-2-20 08:31
我想学,我们一对一培训好了,
作者:
zhuyiwen
时间:
2012-2-20 09:37
yedaoan 发表于 2012-2-20 08:31
我想学,我们一对一培训好了,
你想如何学?你的SQLServer的基础吗?你能支付多少学习费用?
作者:
yedaoan
时间:
2012-2-20 17:45
亦文,留个电话,我打给你,或发到我的邮箱
yedaoan@126.com
作者:
zhuyiwen
时间:
2012-2-20 22:49
还是免了吧
作者:
weed543
时间:
2014-9-15 14:54
可惜了,看不懂得
作者:
WFH6898
时间:
2015-11-22 10:40
基础太差,无法看懂
作者:
gxx6
时间:
2015-12-26 19:22
朱老师,我觉得,这个东西门槛高,曲高和寡,能被培训的人太少。
你需要助手么,老师?
作者:
764300778
时间:
2015-12-26 23:28
EEEE
作者:
whggwu
时间:
2016-3-13 11:27
依旧经典
作者:
jlf001
时间:
2016-4-10 16:04
学习
欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)
Powered by Discuz! X3.3