怎么使Sql Server的存储过程为不能修改状态或不能打开?

野草飞雪 2004-04-12 05:32:06
请教各位:
我们都知道,打开Sql Server中属于系统本身的存储过程,只能看,不能作修改,在系统数据库Master中,属于扩展存储过程,还不能看到内容。
现在请教各位,我们自己定义的存储过程是否也能使它不能修改或不能打开?
...全文
702 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
cherrylin 2004-04-13
  • 打赏
  • 举报
回复
up
zjcxc 元老 2004-04-12
  • 打赏
  • 举报
回复
sp_addextendedproc
将新扩展存储过程的名称注册到 Microsoft® SQL Server™ 上。

语法
sp_addextendedproc [ @functname = ] 'procedure' ,
[ @dllname = ] 'dll'

参数
[ @functname = ] 'procedure'

在动态链接库 (DLL) 内调用的函数名称。procedure 的数据类型为 nvarchar(517),没有默认设置。procedure 能够可选地包含 owner.function 形式的所有者名称。

[ @dllname = ] 'dll'

包含该函数的 DLL 名称。dll 的数据类型为 varchar(255),没有默认设置。

返回代码值
0(成功)或 1(失败)

结果集


注释
使用 Microsoft Open Data Services 的程序员可以创建扩展存储过程。创建扩展存储过程后,必须使用 sp_addextendedproc 将此存储过程添加到 SQL Server 上。有关更多信息,请参见创建扩展存储过程。

只将一个扩展存储过程添加到 master 数据库中。若要从非 master 的数据库中执行扩展存储过程,请用 master 限定扩展存储过程的名称。

sp_addextendedproc 将条目添加到 sysobjects 中,用 SQL Server 注册新扩展存储过程名称。同样在 syscomments 表中添加一个条目。

权限
只有 sysadmin 固定服务器角色的成员才可以执行 sp_addextendedproc。

示例
下例添加 xp_hello 扩展存储过程。

USE master
EXEC sp_addextendedproc xp_hello, 'xp_hello.dll'

zjcxc 元老 2004-04-12
  • 打赏
  • 举报
回复
Creating Extended Stored Procedures
An extended stored procedure is a function with a prototype:

SRVRETCODE xp_extendedProcName (SRVPROC *);

Using the prefix "xp_" is optional. Extended stored procedure names are case sensitive when referenced in Transact-SQL statements, regardless of code page/sort order installed on the server. An extended stored procedure is implemented in a 32-bit dynamic-linked library (DLL). When you build a DLL:

If an entry point is necessary, write a DllMain function.
This function is optional; if you do not provide it in source code, the compiler links its own version, which does nothing but return TRUE. If you provide a DllMain function, the operating system calls this function when a thread or process attaches to or detaches from the DLL.

All functions called from outside the DLL (all extended stored procedure functions) must be exported.
You can export a function by listing its name in the EXPORTS section of a .def file, or you can prefix the function name in the source code with __declspec(dllexport), a Microsoft compiler extension (Note that __declspec() begins with two underscores).

These Open Data Services files are required for creating an extended stored procedure DLL.

File Description
Srv.h Open Data Services header file
Opends60.lib Import library for Opends60.dll


It is highly recommended that all Microsoft® SQL Server™ 2000 extended stored procedure DLLs implement and export the following function:

__declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}



Note __declspec(dllexport) is a Microsoft-specific compiler extension. If your compiler does not support this directive, you should export this function in your DEF file under the EXPORTS section.


When SQL Server is started with the trace flag -T260 or if a user with system administrator privileges runs DBCC TRACEON (260), then if the extended stored procedure DLL does not support __GetXpVersion(), a warning message (Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().) is printed to the error log (Note that __GetXpVersion() begins with two underscores). If you get this message, and you are running an extended stored procedure DLL compiled with headers and libraries from SQL Server version 6.x, refer to Level 1: Handling Discontinued Functionality. If you get this message and are running an extended stored procedure DLL compiled with headers and libraries from SQL Server 7.0, your extended stored procedure DLL is not exporting the function __GetXpVersion().

If the extended stored procedure DLL exports __GetXpVersion(), but the version returned by the function is less than that required by the server, a warning message (Error 8132: Extended stored procedure DLL '%' reports its version is %d.%d. Server expects version %d.%d.) stating the version returned by the function and the version expected by the server is printed to the error log. If you get this message, you are returning an incorrect value from __GetXpVersion(), or you are compiling with an older version of srv.h.



Note SetErrorMode, a Microsoft Win32® function, should not be called in extended stored procedures.


For more information about creating a DLL, see the development environment documentation and the Microsoft Win32 SDK documentation.

To create an extended stored procedure DLL by using Microsoft Visual C++

Create a new project of type Win32 Dynamic Link Library.


Set the directory for include files and library files to C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Include and C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib, respectively.
On the Tools menu, click Options.


In the Options dialog box, click the Directories tab and set the directory for include files and library files.
On the Project menu, click Settings.


In the Project Settings dialog box, click the Link tab. Click the General category, and then add opends60.lib to object/library modules.


Add source files (.c, .cpp, and .rc files, and so on) to your project.


Compile and link your project.
zjcxc 元老 2004-04-12
  • 打赏
  • 举报
回复
所以你还要配合权限设置,不给予用户修改存储过程的权限.

另外,对于扩展存储过程,因为扩展存储过程就是调用DLL来处理,所以你当然看不到处理代码,你也可以自己编写DLL来扩充扩展存储过程. 这样别人就肯定看不到你的代码了.
zjcxc 元老 2004-04-12
  • 打赏
  • 举报
回复
--当然,上述方法有解密的方法:

/*--破解函数,过程,触发器,视图.仅限于SQLSERVER2000

--作者:J9988--*/
/*--调用示例

--解密指定存储过程
exec sp_decrypt 'AppSP_test'

--对所有的存储过程解密
declare tb cursor for
select name from sysobjects where xtype='P' and status>0

declare @name sysname
open tb
fetch next from tb into @name
while @@fetch_status=0
begin
print '/*-------存储过程 ['+@name+'] -----------*/'
exec sp_decrypt @name
fetch next from tb into @name
end
close tb
deallocate tb
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_DECRYPT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_DECRYPT]
GO

CREATE PROCEDURE SP_DECRYPT
@objectName sysname
AS
set nocount on
begin tran
declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000),@sql5 nvarchar(4000),@sql6 nvarchar(4000),@sql7 nvarchar(4000),@sql8 nvarchar(4000),@sql9 nvarchar(4000),@sql10 nvarchar(4000)
DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare @i int,@status int,@type varchar(10),@parentid int
declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)

create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)
insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName)
select @number=max(number) from #temp
set @k=0

while @k<=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
if @type='P'
set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '
end)

if @type='TR'
set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '

if @type='FN' or @type='TF' or @type='IF'
set @sql1=(case @type when 'TF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)

if @type='V'
set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '

set @q=len(@sql1)
set @sql1=@sql1+REPLICATE('-',4000-@q)
select @sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)
exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
end
set @k=@k+1
end

set @k=0
while @k<=@number
begin

if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
select @colid=max(colid) from #temp where number=@k
set @n=1

while @n<=@colid
begin
select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
if @n=1
begin
if @type='P'
SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
end)


if @type='FN' or @type='TF' or @type='IF'--刚才有错改一下
SET @OrigSpText2=(case @type when 'TF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)

if @type='TR'
set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '

if @type='V'
set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '

set @q=4000-len(@OrigSpText2)
set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET @OrigSpText2=REPLICATE('-', 4000)
end
--start counter
SET @i=1
--fill temporary variable
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))

--loop
WHILE @i<=datalength(@OrigSpText1)/2
BEGIN

SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET @i=@i+1
END
set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
set @resultsp=(case when @encrypted=1
then @resultsp
else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
end)
print @resultsp
--execute( @resultsp)
set @n=@n+1

end

end
set @k=@k+1
end

drop table #temp
rollback tran
set nocount off
go
zjcxc 元老 2004-04-12
  • 打赏
  • 举报
回复
--加密
CREATE PROC procedure_name
...
WITH ENCRYPTION
as
...
minajo21 2004-04-12
  • 打赏
  • 举报
回复
没试过,不知道根据用户设权限能不能行。

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧