{src}
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 --
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')

-- FINALIZATION: RETURN SUCCESS/FAILURE --
raiserror(15298,-1,-1)
return (0) -- sp_addlogin
<CODE>
{/src}

{email=admin@sql<>.ru}Admin{/email}

{email=admin@sql.ru}Admin{/email}

{url=www.sql.ru/forum}Все в форум!{/url}

Все в форум!

Все в форум!

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

-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
0
if exists(select * from master.dbo.syslogins where loginname = @loginame)
begin
raiserror(15025,-1,-1,@loginame)
return (1)
end

-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
1
IF db_id(@defdb) IS NULL
begin
raiserror(15010,-1,-1,@defdb)
return (1)
end

-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
2
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 -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
3

if @deflanguage is null
select @deflanguage = N'us_english'
end

-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
4
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

-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
5
declare @xstatus smallint
select @xstatus = 2 -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
6
if @encryptopt is null
select @passwd = pwdencrypt(@passwd)
else if @encryptopt = 'skip_encryption_old'
begin
select @xstatus = @xstatus | 0x800, -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
7
@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

-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
8
INSERT INTO master.dbo.sysxlogins VALUES
(NULL, @sid, @xstatus, getdate(),
getdate(), @loginame, convert(varbinary(256), @passwd),
db_id(@defdb), @deflanguage)
if @@error <> 0 -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
9
return (1)

-- return value of sp call
0
exec('securityadmin'0)

-- return value of sp call
1
raiserror(15298,-1,-1)
return (0) -- return value of sp call
2

jud<g>e писал:

Какие нибудь цитаты из пред. письма например
или еще откуда