一个用户管理的数据库脚本

zhuyiwen · 发表于 2012-1-13 02:06:51

想写一个通用的用户管理数据库，因此，今天写了如下 T-SQL 脚本，练习自己的写脚本能力。现在放在这里，也许对一些网友有作用。呵呵......

2012-01-12
朱亦文

---=========================================================================---
--- 创建 [Person] 数据库
--- 日期：2011-01-12
---=========================================================================---
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
--------------------------------------------------
--- 表：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
--- 创建系统帐户
SET IDENTITY_INSERT [dbo].[USysUsers] ON
GO
INSERT [dbo].[USysUsers]
([UID],[UserName],[Password],[IsSystem],[Description],[Enabled]) VALUES
(0, N'Guest', '', 1, N'匿名来宾用户', 0)
INSERT [dbo].[USysUsers]
([UID],[UserName],[Password],[IsSystem],[Description],[Enabled]) VALUES
(1, N'Administrator', 'sys', 1, N'系统管理员', 1)
GO
SET IDENTITY_INSERT [dbo].[USysUsers] OFF
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
--------------------------------------------------
--- 过程：_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
--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 表：USysUserExtender
--- 用途：存储用户扩展属性
--- 依赖：dbo.USysUsers
--------------------------------------------------
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
)
)
GO
--- 设置 USysUserExtender 与 USysUsers 关系
--- 级联更新、级联删除
ALTER TABLE [dbo].[USysUserExtender]
WITH
CHECK ADD CONSTRAINT [FK_USysUserExtender_USysUsers]
FOREIGN KEY([UID]) REFERENCES [dbo].[USysUsers] ([UID])
ON UPDATE CASCADE
ON DELETE CASCADE
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
--------------------------------------------------
--- 视图：vUsersAll
--- 用途：所有用户
--- 依赖：dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersAll]
AS
SELECT * FROM [dbo].[_ftUSysUsers](NULL, NULL)
GO
--------------------------------------------------
--- 视图：vUsersSystem
--- 用途：所有系统用户
--- 依赖：dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersSystem]
AS
SELECT * FROM [dbo].[_ftUSysUsers](1, NULL)
GO
--------------------------------------------------
--- 视图：vUsersSystemCurrent
--- 用途：当前系统用户
--- 依赖：dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersSystemCurrent]
AS
SELECT * FROM [dbo].[_ftUSysUsers](1, 1)
GO
--------------------------------------------------
--- 视图：vUsersSystemDisabled
--- 用途：当前禁用的系统用户
--- 依赖：dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersSystemDisabled]
AS
SELECT * FROM [dbo].[_ftUSysUsers](1, 0)
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
--------------------------------------------------
--- 视图：vUsersGeneralDisabled
--- 用途：当前禁用的一般用户
--- 依赖：dbo.USysUsers/dbo.USysUserExtender/dbo._ftUSysUsers
--------------------------------------------------
CREATE VIEW [dbo].[vUsersGeneralDisabled]
AS
SELECT * FROM [dbo].[_ftUSysUsers](0, 0)
GO

复制代码

zhuyiwen · 发表于 2012-1-13 02:07:27

--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 过程：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
--------------------------------------------------
--- 过程：paUserEditExtender
--- 用途：修改用户扩展属性
--- 返回：0-不成功
--- 依赖：dbo.USysUserExtender
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUserEditExtender]
(
@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
--------------------------------------------------
--- 过程：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

复制代码

daxin1 · 发表于 2012-1-13 07:15:04

先顶再顶我顶顶顶{:soso_e112:}
终于住一楼了

xuwenning · 发表于 2012-1-13 08:17:08

谢谢分享收藏了

tmtony · 发表于 2012-1-14 09:23:35

谢谢老朱分享

zhuyiwen · 发表于 2012-1-15 20:07:22

2012-01-15 新增

--------------------------------------------------
--- 过程：paUsersDel
--- 用途：删除指定用户
--- 返回：0-不成功
--- 依赖：dbo.USysUsers
--------------------------------------------------
CREATE PROCEDURE [dbo].[paUsersDel]
(
@UID int
)
AS
BEGIN
DELETE [dbo].[USysUsers] WHERE [UID] = @UID
RETURN @@ROWCOUNT
END
GO

复制代码

yodong · 发表于 2012-1-15 22:03:02

朱大哥现在用sql2008了?

zhuyiwen · 发表于 2012-1-15 22:42:27

没有，那个太庞大了，朱大哥用不起，呵呵。

风中漫步 · 发表于 2012-1-17 10:15:04

谢谢分享

先复制,偶功力太浅要慢慢研究

bbpst · 发表于 2012-1-31 10:10:10

{:soso_e179:}学习

帐号		自动登录	找回密码
密码			注册

群介绍
Access培训群 (792054000)
Access免费培训群2 (792054000)
Access交流总群 (5004108)
Access中国官方群 (162780177)

主题相关群

地区相关群[您还没设置居住地]

一个用户管理的数据库脚本

本帖子中包含更多资源

本帖被以下淘专辑推荐:

相关帖子

群介绍
主题无相 (随机显示版	同地区群块其他地区)