前言

我在2011年刚入门学习的时候，是从 SQL Server 2008 开始学的，再加上这些年较少接触 SQL Server 2000，因此对它不是很熟。
之前都是在 SQL Server 2000 企业管理器 界面上操作创建角色、用户、授权，对如何用SQL语句操作就更不熟悉了...
上周接手了个老项目，就重新学习了一下 SQL Server 2000。

例如创建一个用于连接访问数据库的登陆名，
在 SQL Server 2000 中，只能使用系统存储过程提供的 sp_addlogin 创建登录名。
而在 SQL Server 2005 以上版本中，可以使用 CREATE LOGIN YourLoginUserName WITH PASSWORD YourLoginPassword 语句创建登录名。

给我感觉，SQL Server 2000 在创建角色、用户、授权等SQL语句，跟 SQL Server 2005 以上差异很大。

正文

以abc_db数据库为例

USE [abc_db]
GO

--在当前数据库创建角色
EXEC sp_addrole 'abc_role'

--批量授权指定角色操作当前数据库的表、视图、存储过程
DECLARE @sql NVARCHAR(4000);

SELECT @sql = @sql + N'GRANT EXECUTE ON dbo.' + QUOTENAME(name) + N' TO [abc_role];' FROM sysobjects WHERE type = N'P';
EXEC sp_executesql @sql;

SET @sql = N'';
SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [abc_role];' FROM sysobjects WHERE type = N'U';
EXEC sp_executesql @sql;

SET @sql = N'';
SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [abc_role];' FROM sysobjects WHERE type = N'V';
EXEC sp_executesql @sql;

--创建登陆用户，并且指定默认数据库
sp_addlogin  @loginame = 'abc_login', @passwd =  '123456' , @defdb =  'abc_db', @deflanguage = '简体中文'

 --为登陆用户 abc 在当前数据库中添加安全账户 abc_user
EXEC sp_grantdbaccess 'abc_login','abc_user'

--添加安全用户 abc_user 为角色 abc_role 的成员
EXEC sp_addrolemember 'abc_role','abc_user'

到这里，就可以用abc_login这个登陆名访问abc_db数据库，对表和视图进行增删改查、执行存储过程这些基本操作了。

如果需要更进一步的权限，可以额外加一句SQL，给abc_role角色赋予abc_db数据库所有者权限

EXEC sp_addrolemember 'db_owner','abc_role'

或者给abc_login登陆名赋予abc_db数据库所有者权限

EXEC sp_addrolemember 'db_owner','abc_login'

如果想要为abc_login这个登陆名赋予更大的系统权限，允许访问和操作所有数据库，可以执行这一句SQL，等同sa用户权限。

EXEC sp_addsrvrolemember @loginame = N'abc_login', @rolename = N'sysadmin'

引用

1. https://dba.stackexchange.com/questions/96709/grant-permissions-to-all-tables-views-procedures-in-sql-server-2000
2. https://www.cnblogs.com/zhou5791759/archive/2009/01/17/1377448.html
3. https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-addlogin-transact-sql
4. https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-login-transact-sql