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
-- 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
(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 -- INSERT INTO master.dbo.sysxlogins VALUES (NULL, @sid, @xstatus, getdate(), getdate(), @loginame, convert(varbinary(256), @passwd), db_id(@defdb), @deflanguage) if @@error <> 0 -- this indicates we saw duplicate row return (1)
-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE
SYSLOGINS CHANGE -- exec(''''use master grant all to null'''')
create procedure 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
-- DISALLOW USER TRANSACTION -- set implicit_transactions off IF (@@trancount > 0) begin raiserror(15002,-1,-1,''''sp_addsrvrolemember'''') return (1) end
-- CANNOT CHANGE SA ROLES -- if @loginame = ''''sa'''' begin raiserror(15405, -1 ,-1, @loginame) return (1) end
-- 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
-- ADD ROW FOR NT LOGIN IF NEEDED -- if not exists(select * from master.dbo.syslogins where
loginname = @loginame) begin execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame if (@ret <> 0) begin raiserror(15007,-1,-1,@loginame) return (1) end 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'''')
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
