MS SQL Server 2005 用户权限设置方案

一、首先创建 SQL Server 登录名
位置：./安全性/登录名 下创建登录名
设置好密码过期策略等
指定默认数据库和默认语言
此用户可以登录 SQL Server 服务器
此处设置为：
oh_sql_login_lx/****** 默认数据库 Guise_v1
oh_sql_login_eLd/****** 默认数据库 Guise_v1e
oh_sql_login_5sd0/****** 默认数据库 ZdSkin
对应的 SQL 脚本为：
CREATE LOGIN [oh_sql_login_lx] WITH PASSWORD=N'******'
 , DEFAULT_DATABASE = [Guise_v1]
 , DEFAULT_LANGUAGE = [Simplified Chinese];
GO
CREATE LOGIN [oh_sql_login_eLd] WITH PASSWORD=N'******'
 , DEFAULT_DATABASE = [Guise_v1e]
 , DEFAULT_LANGUAGE = [Simplified Chinese];
GO
CREATE LOGIN [oh_sql_login_5sd0] WITH PASSWORD=N'******'
 , DEFAULT_DATABASE = [ZdSkin]
 , DEFAULT_LANGUAGE = [Simplified Chinese];
GO

二、在指定的业务数据库里面创建用户
位置：./数据库/testdb/安全性/用户
设置用户名（一般与“登录名”一致）和指定“登录名”（第一条设置的 SQL Server 登录用户）
指定默认架构，例如 db_datareader 或 db_datawriter
此用户拥有的架构无需设置
数据库角色成员身份设置为 db_datareader 或 db_datawriter 即可
此处设置为：
oh_db_hepz 默认架构 db_datawriter 数据库角色成员身份 db_datareader 和 db_datawriter

USE [Guise_v1]
CREATE USER [oh_db_lx] FOR LOGIN [oh_sql_login_lx] WITH DEFAULT_SCHEMA = [db_datawriter]
EXEC SP_ADDROLEMEMBER [db_datareader], [oh_db_lx]
EXEC SP_ADDROLEMEMBER [db_datawriter], [oh_db_lx]

USE [Guise_v1e]
CREATE USER [oh_db_eLd] FOR LOGIN [oh_sql_login_eLd] WITH DEFAULT_SCHEMA = [db_datawriter]
EXEC SP_ADDROLEMEMBER [db_datareader], [oh_db_eLd]
EXEC SP_ADDROLEMEMBER [db_datawriter], [oh_db_eLd]

USE [ZdSkin]
CREATE USER [oh_db_5sd0] FOR LOGIN [oh_sql_login_5sd0] WITH DEFAULT_SCHEMA = [db_datawriter]
EXEC SP_ADDROLEMEMBER [db_datareader], [oh_db_5sd0]
EXEC SP_ADDROLEMEMBER [db_datawriter], [oh_db_5sd0]

-- 建立需要跨库的数据库用户
-- 理解起来有点别扭
-- 实际上是登录用户的是登录的是Guise_v1，用户名是 [oh_db_lx]
-- 那么在这个用户访问 ZdSkin 时，会检测 ZdSkin 里面的数据库同名用户映射的是哪个 SQL 登录账户
-- 意思是：哪个数据库账户要跨库，这个账户名就要同时出现在多个相关库的数据库用户里面，同一个名字映射各自的 SQL 登录账户
USE [ZdSkin]
GO
CREATE USER [oh_db_lx] FOR LOGIN [oh_sql_login_lx] WITH DEFAULT_SCHEMA = [db_datawriter]
GO
EXEC SP_ADDROLEMEMBER [db_datareader], [oh_db_lx]
GO
EXEC SP_ADDROLEMEMBER [db_datawriter], [oh_db_lx]
GO
-- 建立需要跨库的数据库用户
USE [ZdSkin]
GO
CREATE USER [oh_db_eLd] FOR LOGIN [oh_sql_login_eLd] WITH DEFAULT_SCHEMA = [db_datawriter]
GO
EXEC SP_ADDROLEMEMBER [db_datareader], [oh_db_eLd]
GO
EXEC SP_ADDROLEMEMBER [db_datawriter], [oh_db_eLd]
GO

三、每个存储过程、函数需要单独设置权限
在存储过程的属性/权限 中的 用户或角色
增加 用户并设置 Execute 权限即可

先拿到全部存储过程，组织成 SQL 语句
USE [Guise_v1]
select 'GRANT EXECUTE ON ['+name+'] TO [oh_db_lx] ' as [存储过程名称] from sysobjects where xtype='P'

USE [Guise_v1e]
select 'GRANT EXECUTE ON ['+name+'] TO [oh_db_eLd] ' as [存储过程名称] from sysobjects where xtype='P'

USE [ZdSkin]
select 'GRANT EXECUTE ON ['+name+'] TO [oh_db_5sd0] ' as [存储过程名称] from sysobjects where xtype='P'

然后得到组织好的 SQL 语句执行

函数只能自己写了
USE [Guise_v1]
-- 表值函数
GRANT REFERENCES ON [GetChildrenID2Table] TO [oh_db_lx]
GRANT REFERENCES ON [GetExpChildrenId2Table] TO [oh_db_lx]
GRANT REFERENCES ON [Split2Rows] TO [oh_db_lx]
GRANT SELECT ON [GetChildrenID2Table] TO [oh_db_lx]
GRANT SELECT ON [GetExpChildrenId2Table] TO [oh_db_lx]
GRANT SELECT ON [Split2Rows] TO [oh_db_lx]
-- 标量函数
GRANT REFERENCES ON [f_Int2IP] TO [oh_db_lx]
GRANT REFERENCES ON [f_IP2Int] TO [oh_db_lx]
GRANT REFERENCES ON [FormatDateTime] TO [oh_db_lx]
GRANT REFERENCES ON [Fun_ColorMerge] TO [oh_db_lx]
GRANT REFERENCES ON [Fun_EquipPath] TO [oh_db_lx]
GRANT REFERENCES ON [MD5] TO [oh_db_lx]
GRANT EXECUTE ON [f_Int2IP] TO [oh_db_lx]
GRANT EXECUTE ON [f_IP2Int] TO [oh_db_lx]
GRANT EXECUTE ON [FormatDateTime] TO [oh_db_lx]
GRANT EXECUTE ON [Fun_ColorMerge] TO [oh_db_lx]
GRANT EXECUTE ON [Fun_EquipPath] TO [oh_db_lx]
GRANT EXECUTE ON [MD5] TO [oh_db_lx]

USE [Guise_v1e]
-- 表值函数
GRANT REFERENCES ON [GetChildrenID2Table] TO [oh_db_eLd]
GRANT SELECT ON [GetChildrenID2Table] TO [oh_db_eLd]
GRANT REFERENCES ON [GetExpChildrenId2Table] TO [oh_db_eLd]
GRANT SELECT ON [GetExpChildrenId2Table] TO [oh_db_eLd]
GRANT REFERENCES ON [Split2Rows] TO [oh_db_eLd]
GRANT SELECT ON [Split2Rows] TO [oh_db_eLd]
-- 标量函数
GRANT REFERENCES ON [FormatDateTime] TO [oh_db_eLd]
GRANT EXECUTE ON [FormatDateTime] TO [oh_db_eLd]
GRANT REFERENCES ON [Fun_ColorMerge] TO [oh_db_eLd]
GRANT EXECUTE ON [Fun_ColorMerge] TO [oh_db_eLd]
GRANT REFERENCES ON [Fun_EquipPath] TO [oh_db_eLd]
GRANT EXECUTE ON [Fun_EquipPath] TO [oh_db_eLd]
GRANT REFERENCES ON [MD5] TO [oh_db_eLd]
GRANT EXECUTE ON [MD5] TO [oh_db_eLd]

USE [ZdSkin]
-- 表值函数
GRANT REFERENCES ON [GetPtnChildrenID2Table] TO [oh_db_5sd0]
GRANT SELECT ON [GetPtnChildrenID2Table] TO [oh_db_5sd0]
-- 标量函数
GRANT REFERENCES ON [FormatDateTime] TO [oh_db_5sd0]
GRANT EXECUTE ON [FormatDateTime] TO [oh_db_5sd0]