SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOcreate procedure sp_addapprole @rolename sysname, -- name of new app role @password sysname -- password for app role as -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @ret int, -- return value of sp call @uid smallint -- CHECK FOR NULL PASSWORD if (@password is null) begin raiserror(15034,-1,-1) return (1) end -- CHECK PERMISSIONS -- if (not is_member('db_securityadmin') = 1) and (not is_member('db_owner') = 1) begin raiserror(15247,-1,-1) return (1) end -- DISALLOW USER TRANSACTION -- set implicit_transactions off if (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_addapprole') return (1) end -- VALIDATE APPROLE NAME -- execute @ret = sp_validname @rolename if @ret <> 0 return (1) if (charindex('\', @rolename) > 0) begin raiserror(15006,-1,-1,@rolename) return (1) end -- ERROR IF SYSUSERS NAME ALREADY EXISTS -- if user_id(@rolename) is not null OR @rolename IN ('system_function_schema','INFORMATION_SCHEMA') begin raiserror(15363,-1,-1,@rolename) return (1) end -- OBTAIN NEW APPROLE UID (RESERVE 1-4) -- if user_name(5) IS NULL select @uid = 5 else select @uid = min(uid)+1 from sysusers where uid >= 5 and uid < (16384 - 1) -- stay in users range and user_name(uid+1) is null -- uid not in use if @uid is null begin raiserror(15065,-1,-1) return (1) end -- INSERT THE ROW INTO SYSUSERS -- insert into sysusers values (@uid, 32, @rolename, NULL, 0x00, getdate(), getdate(), 1, convert(varbinary(256), pwdencrypt(@password))) -- FINALIZATION: PRINT/RETURN SUCCESS -- if @@error <> 0 return (1) raiserror(15425,-1,-1) return (0) -- sp_addapproleGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO系统存储过程里面很多!!! 喜欢木吉他,???
晕。。数据库中不是有好多系统存储过程吗》那可都是高手写的呀 我就知道 create proc @.. as ..
GO
SET ANSI_NULLS OFF
GOcreate procedure sp_addapprole
@rolename sysname, -- name of new app role
@password sysname -- password for app role
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int, -- return value of sp call
@uid smallint -- CHECK FOR NULL PASSWORD
if (@password is null)
begin
raiserror(15034,-1,-1)
return (1)
end -- CHECK PERMISSIONS --
if (not is_member('db_securityadmin') = 1) and
(not is_member('db_owner') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end -- DISALLOW USER TRANSACTION --
set implicit_transactions off
if (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sp_addapprole')
return (1)
end -- VALIDATE APPROLE NAME --
execute @ret = sp_validname @rolename
if @ret <> 0
return (1)
if (charindex('\', @rolename) > 0)
begin
raiserror(15006,-1,-1,@rolename)
return (1)
end -- ERROR IF SYSUSERS NAME ALREADY EXISTS --
if user_id(@rolename) is not null OR
@rolename IN ('system_function_schema','INFORMATION_SCHEMA')
begin
raiserror(15363,-1,-1,@rolename)
return (1)
end -- OBTAIN NEW APPROLE UID (RESERVE 1-4) --
if user_name(5) IS NULL
select @uid = 5
else
select @uid = min(uid)+1 from sysusers
where uid >= 5 and uid < (16384 - 1) -- stay in users range
and user_name(uid+1) is null -- uid not in use
if @uid is null
begin
raiserror(15065,-1,-1)
return (1)
end -- INSERT THE ROW INTO SYSUSERS --
insert into sysusers values
(@uid, 32, @rolename, NULL, 0x00, getdate(),
getdate(), 1, convert(varbinary(256), pwdencrypt(@password))) -- FINALIZATION: PRINT/RETURN SUCCESS --
if @@error <> 0
return (1)
raiserror(15425,-1,-1)
return (0) -- sp_addapproleGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO系统存储过程里面很多!!!
喜欢木吉他,???
我就知道
create proc
@..
as
..
as
....