如何在一个SQL Server 2000中创建角色、登录用户、安全用户并批量授予权限？

前言 我在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; --创建登陆用户，并且指定默认数据库 EXEC 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' 其它 sp_addlogin 存储过程 源码 --EXEC sp_addlogin @loginame = 'abc_login', @passwd = '123456' , @defdb = 'abc_db', @deflanguage = '简体中文' USE [master] GO /****** Object: StoredProcedure [dbo].[sp_addlogin] Script Date: 2022/5/9 9:40:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[sp_addlogin] @loginame sysname ,@passwd sysname = Null ,@defdb sysname = 'master' -- UNDONE: DEFAULT CONFIGURABLE??? ,@deflanguage sysname = Null ,@sid varbinary(16) = Null ,@encryptopt varchar(20) = Null AS -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on Declare @ret int -- return value of sp call -- CHECK PERMISSIONS -- IF (not is_srvrolemember('securityadmin') = 1) begin dbcc auditevent (104, 1, 0, @loginame, NULL, NULL, @sid) raiserror(15247,-1,-1) return (1) end ELSE begin dbcc auditevent (104, 1, 1, @loginame, NULL, NULL, @sid) end -- DISALLOW USER TRANSACTION -- set implicit_transactions off IF (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_addlogin') return (1) end -- VALIDATE LOGIN NAME AS: -- (1) Valid SQL Name (SQL LOGIN) -- (2) No backslash (NT users only) -- (3) Not a reserved login name execute @ret = sp_validname @loginame if (@ret <> 0) return (1) if (charindex('\', @loginame) > 0) begin raiserror(15006,-1,-1,@loginame) return (1) end --Note: different case sa is allowed. if (@loginame = 'sa' or lower(@loginame) in ('public')) begin raiserror(15405, -1 ,-1, @loginame) return (1) end -- LOGIN NAME MUST NOT ALREADY EXIST -- if exists(select * from master.dbo.syslogins where loginname = @loginame) begin raiserror(15025,-1,-1,@loginame) return (1) end -- VALIDATE DEFAULT DATABASE -- IF db_id(@defdb) IS NULL begin raiserror(15010,-1,-1,@defdb) return (1) end -- VALIDATE DEFAULT LANGUAGE -- IF (@deflanguage IS NOT Null) begin Execute @ret = sp_validlang @deflanguage IF (@ret <> 0) return (1) end ELSE begin select @deflanguage = name from master.dbo.syslanguages where langid = @@default_langid --server default language if @deflanguage is null select @deflanguage = N'us_english' end -- VALIDATE SID IF GIVEN -- if ((@sid IS NOT Null) and (datalength(@sid) <> 16)) begin raiserror(15419,-1,-1) return (1) end else if @sid is null select @sid = newid() if (suser_sname(@sid) IS NOT Null) begin raiserror(15433,-1,-1) return (1) end -- VALIDATE AND USE ENCRYPTION OPTION -- declare @xstatus smallint select @xstatus = 2 -- access if @encryptopt is null select @passwd = pwdencrypt(@passwd) else if @encryptopt = 'skip_encryption_old' begin select @xstatus = @xstatus | 0x800, -- old-style encryption @passwd = convert(sysname, convert(varbinary(30), convert(varchar(30), @passwd))) end else if @encryptopt <> 'skip_encryption' begin raiserror(15600,-1,-1,'sp_addlogin') return 1 end -- ATTEMPT THE INSERT OF THE NEW LOGIN -- BEGIN TRAN INSERT INTO master.dbo.sysxlogins VALUES (NULL, @sid, @xstatus, getdate(), getdate(), @loginame, convert(varbinary(256), @passwd), db_id(@defdb), @deflanguage) -- check that there are no duplicate rows with the same name if @@error <> 0 or exists(select * from master.dbo.sysxlogins with (nolock) where srvid IS NULL and name = @loginame and sid <> @sid) begin raiserror(15025,-1,-1,@loginame) ROLLBACK TRAN return (1) end COMMIT TRAN -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE -- exec('use master grant all to null') -- FINALIZATION: RETURN SUCCESS/FAILURE -- raiserror(15298,-1,-1) return (0) -- sp_addlogin sp_adduser 存储过程 源码 -- EXEC sp_adduser @loginame = 'abc_login', @name_in_db = 'abc_db' , @grpname = 'db_owner' USE [master] GO /****** Object: StoredProcedure [dbo].[sp_adduser] Script Date: 2022/5/9 9:37:41 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[sp_adduser] @loginame sysname, -- user's login name in syslogins @name_in_db sysname = NULL, -- user's name to add to current db @grpname sysname = NULL -- role to which user should be added. as -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @ret int -- LIMIT TO SQL/NT USERS IN SYSLOGINS (BCKWRD COMPAT ONLY!) if not exists (select * from master.dbo.syslogins where loginname = @loginame and (isntuser = 1 or isntname = 0)) and @loginame <> 'guest' begin raiserror(15007,-1,-1,@loginame) return (1) end -- VALIDATE THE ROLENAME -- if @grpname is not null and not exists (select * from sysusers where name = @grpname and issqlrole = 1) begin raiserror(15014,-1,-1,@grpname) return (1) end if @name_in_db is null select @name_in_db = @loginame -- In Hydra only the user dbo can do this -- if (not is_member('dbo') = 1) begin -- AUDIT FAILED SECURITY CHECK -- dbcc auditevent (109, 1, 0, @loginame, @name_in_db, @grpname , NULL) raiserror(15247,-1,-1) return (1) end else begin -- AUDIT SUCCESSFUL SECURITY CHECK -- dbcc auditevent (109, 1, 1, @loginame, @name_in_db, @grpname , NULL) end -- ADD THE USER TO THE DATABASE -- execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT if (@ret <> 0) return (1) -- ADD USER TO ROLE IF GIVEN. NOP FOR 'public' -- if (@grpname is not null) and (@grpname <> 'public') begin execute @ret = sp_addrolemember @grpname, @name_in_db if @ret <> 0 begin -- ROLL BACK THE ABOVE sp_grantdbaccess -- if @name_in_db = 'guest' update sysusers set status = status & ~2, updatedate = getdate() where name = 'guest' else delete from sysusers where name = @name_in_db return (1) end end -- RETURN SUCCESS -- return (0) -- sp_adduser sp_grantdbaccess 存储过程 源码 --EXEC sp_grantdbaccess 'abc_login','abc_user' USE [master] GO /****** Object: StoredProcedure [dbo].[sp_grantdbaccess] Script Date: 2022/5/9 9:35:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[sp_grantdbaccess] @loginame sysname, @name_in_db sysname = NULL OUT as -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @ret int, -- return value of sp call @uid smallint, @sid varbinary(85), @status smallint if @name_in_db is null select @name_in_db = @loginame -- CHECK PERMISSIONS -- if (not is_member('db_accessadmin') = 1) and (not is_member('db_owner') = 1) begin dbcc auditevent (109, 3, 0, @loginame, @name_in_db, NULL, NULL) raiserror(15247,-1,-1) return (1) end else begin dbcc auditevent (109, 3, 1, @loginame, @name_in_db, NULL, NULL) end -- DISALLOW USER TRANSACTION -- set implicit_transactions off IF (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_grantdbaccess') return (1) end -- VALIDATE NAME-IN-DB -- if @name_in_db <> @loginame begin exec @ret = sp_validname @name_in_db if @ret <> 0 return(1) if (charindex('\', @name_in_db) > 0) begin raiserror(15006,-1,-1,@name_in_db) return (1) end end -- CHECK FOR SPECIAL USER GUEST -- if @name_in_db = 'guest' begin -- ERROR IF NOT USER, OR ALREADY ADDED -- if @loginame <> 'guest' begin raiserror(15062,-1,-1) return(1) end if exists (select * from sysusers where hasdbaccess = 1 and name = 'guest') begin raiserror(15023,-1,-1,'guest') return (1) end -- ENABLE USER GUEST -- update sysusers set status = (status & ~1) | 2, updatedate = getdate() where name = 'guest' return (0) end -- VALIDATE LOGIN NAME (OBTAIN SID) -- select @status = case when (charindex('\', @loginame) <> 0) then 4 else 0 end if @status = 0 select @sid = sid from master.dbo.syslogins -- sql user where isntname = 0 and loginname = @loginame if @sid is null begin -- NT GROUPS REQUIRE DOMAIN NAME -- if @status = 4 select @sid = get_sid('\G'+@loginame, NULL) -- nt group if @sid is null begin select @sid = get_sid('\U'+@loginame, NULL) -- nt user if @sid is not null select @status = 12 end end -- PREVENT USE OF CERTAIN LOGINS -- else if @sid = 0x1 -- 'sa' begin raiserror(15405, -1, -1, @loginame) return (1) end if @sid is null begin if @status = 0 raiserror(15007,-1,-1,@loginame) else raiserror(15401,-1,-1,@loginame) return (1) end -- CHECK IF LOGIN ALREADY IN DATABASE -- if exists (select sid from sysusers where sid = @sid) begin -- ERROR IF LOGIN IS ALREADY ALIASED -- if exists (select sid from sysusers where sid = @sid and isaliased = 1) begin raiserror(15022,-1,-1) return (1) end -- ERROR IF ALREADY EXISTS UNDER DIFFERENT NAME -- if (not user_sid(user_id(@name_in_db)) = @sid) begin raiserror(15063,-1,-1) return (1) end -- ERROR IF LOGIN ALREADY HAS ACCESS -- if exists (select sid from sysusers where sid = @sid and hasdbaccess = 1) begin if @status = 4 raiserror(15024,-1,-1,@name_in_db) else raiserror(15023,-1,-1,@name_in_db) return (1) end -- GIVE DATABASE ACCESS TO THIS LOGIN -- update sysusers set status = (status & ~1) | 2, updatedate = getdate() where sid = @sid return @@error end if @name_in_db = 'sys' raiserror(15355,-1,-1) if user_id(@name_in_db) is not null OR @name_in_db IN ('system_function_schema','INFORMATION_SCHEMA') begin -- SYSUSERS NAME ALREADY EXISTS -- if @status = 4 raiserror(15024,-1,-1,@name_in_db) else raiserror(15023,-1,-1,@name_in_db) return (1) end -- OBTAIN NEW UID (RESERVE 1-4) -- if user_name(5) IS NULL select @uid = 5 else select @uid = min(uid)+1 from sysusers where uid >= 5 and uid < (16384 - 1) -- stay in users range and user_name(uid+1) is null -- uid not in use if @uid is null begin raiserror(15065,-1,-1) return (1) end -- INSERT SYSUSERS ROW -- insert into sysusers select @uid, @status | 2, @name_in_db, @sid, 0x00, getdate(), getdate(), 0, NULL -- INVALIDATE CACHED PERMISSIONS -- grant all to null -- PRINT SUCCESS -- raiserror(15341,-1,-1, @loginame) -- RETURN SUCCESS STATUS -- return @@error -- sp_grantdbaccess sp_addrolemember 存储过程 源码 --EXEC sp_addrolemember 'db_owner','abc_role' USE [master] GO /****** Object: StoredProcedure [dbo].[sp_addrolemember] Script Date: 2022/5/9 10:12:56 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_addrolemember] @rolename sysname, @membername sysname AS -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @roluid smallint, @owner smallint, @memuid smallint, @ret int declare @ruidbyte smallint, @ruidbit smallint declare @proc nvarchar(50) -- DISALLOW USER TRANSACTION -- set implicit_transactions off if (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_addrolemember') return (1) end --cannot change membership of public if @rolename = 'public' begin raiserror(15081, -1,-1) return(1) end -- ROLE NAME (OBTAIN OWNER FOR PERMISSIONS) -- select @roluid = uid, @owner = altuid from sysusers where name = @rolename and issqlrole = 1 -- ERROR IF ROLE NOT FOUND OR PUBLIC -- if @roluid is null begin raiserror(15014,-1,-1,@rolename) return (1) end -- CHECK PERMISSIONS -- -- Only member of db_owner can add members to db-fixed roles -- if (not is_member('db_owner') = 1) and (not (@roluid < 16400 and is_member('db_owner') = 1)) and (not (@roluid >= 16400 and is_member('db_securityadmin') = 1)) and (not (@roluid >= 16400 and is_member(user_name(@owner)) = 1)) begin dbcc auditevent (110, 1, 0, NULL, @membername, @rolename, NULL) raiserror(15247,-1,-1) return (1) end else begin dbcc auditevent (110, 1, 1, NULL, @membername, @rolename, NULL) end -- CHECK MEMBER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) -- select @memuid = uid from sysusers where name = @membername and isaliased = 0 if @memuid is null begin execute @ret = sp_MSadduser_implicit_ntlogin @membername select @memuid = uid from sysusers where name = @membername and isaliased = 0 end if @memuid is null begin raiserror(15410, -1, -1, @membername) return (1) end -- CANNOT CHANGE MEMBERSHIP OF FIXED ROLES OR DBO -- if @memuid in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema or (@memuid >= 16384 and @memuid < 16400) begin raiserror(15405, -1 ,-1, @membername) return (1) end -- CHECK FOR CIRCULAR MEMBERSHIPS -- if is_userinrole(@rolename, @membername) = 1 begin raiserror(15413, -1, -1) return (1) end -- SET ROLE BIT FOR THIS USER select @ruidbyte = ((@roluid - 16384) / 8) + 1 , @ruidbit = power(2, @roluid & 7) update sysusers set roles = convert(varbinary(2048), substring(convert(binary(2048), roles), 1, @ruidbyte-1) + convert(binary(1), (@ruidbit) | substring(convert(binary(2048), roles), @ruidbyte, 1)) + substring(convert(binary(2048), roles), @ruidbyte+1, 2048-@ruidbyte) ), updatedate = getdate() where uid = @memuid -- END ROLE BIT MANIPULATION -- INVALIDATE CACHED PERMISSIONS (MEMBERSHIP CHANGES PERMISSIONS) -- select @ret = @@error -- save success state grant all to null -- FINALIZATION: PRINT/RETURN SUCCESS -- if @@error <> 0 or @ret <> 0 return (1) raiserror(15488,-1,-1,@membername,@rolename) return (0) -- sp_addrolemember sp_addsrvrolemember 存储过程 源码 --EXEC sp_addsrvrolemember @loginame = N'abc_login', @rolename = N'sysadmin' USE [master] GO /****** Object: StoredProcedure [dbo].[sp_addsrvrolemember] Script Date: 2022/5/9 10:11:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[sp_addsrvrolemember] @loginame sysname, -- login name @rolename sysname = NULL -- server role name as -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @ret int, -- return value of sp call @rolebit smallint, @ismem int, @sid varbinary(85) -- DISALLOW USER TRANSACTION -- set implicit_transactions off IF (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_addsrvrolemember') return (1) end -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS -- select @ismem = is_srvrolemember(@rolename) if @ismem is null begin dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL) raiserror(15402, -1, -1, @rolename) return (1) end if @ismem = 0 begin dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL) raiserror(15247,-1,-1) return (1) end -- AUDIT A SUCCESSFUL SECURITY CHECK -- dbcc auditevent (108, 1, 1, @loginame, NULL, @rolename, NULL) -- OBTAIN THE BIT FOR THIS ROLE -- select @rolebit = CASE @rolename WHEN 'sysadmin' THEN 16 WHEN 'securityadmin' THEN 32 WHEN 'serveradmin' THEN 64 WHEN 'setupadmin' THEN 128 WHEN 'processadmin' THEN 256 WHEN 'diskadmin' THEN 512 WHEN 'dbcreator' THEN 1024 WHEN 'bulkadmin' THEN 4096 ELSE NULL END select @sid = sid from master.dbo.syslogins where loginname = @loginame -- ADD ROW FOR NT LOGIN IF NEEDED -- if @sid is null begin execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame if (@ret <> 0) begin raiserror(15007,-1,-1,@loginame) return (1) end end -- CANNOT CHANGE SA ROLES -- else if @sid = 0x1 -- 'sa' begin raiserror(15405, -1 ,-1, @loginame) return (1) end -- UPDATE ROLE MEMBERSHIP -- update master.dbo.sysxlogins set xstatus = xstatus | @rolebit, xdate2 = getdate() where name = @loginame and srvid IS NULL -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE -- exec('use master grant all to null') raiserror(15488,-1,-1,@loginame,@rolename) -- FINALIZATION: RETURN SUCCESS/FAILURE return (@@error) -- sp_addsrvrolemember 引用 https://dba.stackexchange.com/questions/96709/grant-permissions-to-all-tables-views-procedures-in-sql-server-2000 https://www.cnblogs.com/zhou5791759/archive/2009/01/17/1377448.html https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-addlogin-transact-sql https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-login-transact-sql