createprocedure sp_addlogin @loginame sysname ,@passwd sysname =Null ,@defdb sysname ='master'-- UNDONE: DEFAULT CONFIGURABLE??? ,@deflanguage sysname =Null ,@sidvarbinary(16) =Null ,@encryptoptvarchar(20) =Null AS -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on Declare@retint-- return value of sp call -- CHECK PERMISSIONS -- IF (notis_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'orlower(@loginame) in ('public')) begin raiserror(15405, -1 ,-1, @loginame) return (1) end -- LOGIN NAME MUST NOT ALREADY EXIST -- ifexists(select*from master.dbo.syslogins where loginname =@loginame) begin raiserror(15025,-1,-1,@loginame) return (1) end -- VALIDATE DEFAULT DATABASE -- IFdb_id(@defdb) ISNULL begin raiserror(15010,-1,-1,@defdb) return (1) end -- VALIDATE DEFAULT LANGUAGE -- IF (@deflanguageISNOTNull) 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@deflanguageisnull select@deflanguage= N'us_english' end -- VALIDATE SID IF GIVEN -- if ((@sidISNOTNull) and (datalength(@sid) <>16)) begin raiserror(15419,-1,-1) return (1) end elseif@sidisnull select@sid=newid() if (suser_sname(@sid) ISNOTNull) begin raiserror(15433,-1,-1) return (1) end -- VALIDATE AND USE ENCRYPTION OPTION -- declare@xstatussmallint select@xstatus=2-- access if@encryptoptisnull select@passwd= pwdencrypt(@passwd) elseif@encryptopt='skip_encryption_old' begin select@xstatus=@xstatus|0x800, -- old-style encryption @passwd=convert(sysname, convert(varbinary(30), convert(varchar(30), @passwd))) end elseif@encryptopt<>'skip_encryption' begin raiserror(15600,-1,-1,'sp_addlogin') return1 end -- ATTEMPT THE INSERT OF THE NEW LOGIN -- BEGINTRAN INSERTINTO 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<>0orexists(select*from master.dbo.sysxlogins with (nolock) where srvid ISNULLand name =@loginameand sid <>@sid) begin raiserror(15025,-1,-1,@loginame) ROLLBACKTRAN return (1) end COMMITTRAN -- 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 GO