create
procedure
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
GO
@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
GO