Server查看视图定义总结,Server中付与顾客查看对

 

 

SQL Server中授予用户查看对象定义的权限

在SQL Server中如何查看数据库视图的定义呢? 其实官方文档已经有一个较详细的总结了,这里在官方文档的基础上,我们再深入展开分析一下,例如如何获取系统视图的定义。知其然知其所以然吗。

在升级一个SQL Server 2000的数据库时,遇到了一致性错误,其中有几个错误是元数据损坏(metadata corruption),特意研究了一下这个案例,因为以前也零零散散的遇到过一些一致性相关错误,但是难得遇到元数据损坏的案例。

 

 

 

在SQL Server中,有时候需要给一些登录名(用户)授予查看所有或部分对象(存储过程、函数、视图、表)的定义权限存。如果是部分存储过程、函数、视图授予查看定义的权限,那么就像下面脚本所示,比较繁琐:

1:使用SQL Server Management Studio(SSMS)

如下所示,数据库从SQL Server 2000还原到SQL Server 2008以后,在做一致性检查时,发现有元数据损坏(metadata corruption),下面是实验是构造的一个测试环境

 

 

 

 

在“对象资源管理器”中,首先找到对应数据库中需要查看定义的视图,右键单击获取对象的定义脚本。这种方式非常简单。在此略过。

DBCC CHECKCATALOG (TEST) WITH NO_INFOMSGS;

GO

 

DBCC CHECKDB(TEST) WITH NO_INFOMSGS;

GO

GRANT VIEW DEFINITION ON  YOUR_PROCEDURE TO   USERNAME;

 

 

 

 

 

GRANT VIEW DEFINITION ON  YOUR_FUNCTION TO USERNAME;

2:通过脚本查看视图的定义。

Msg 8992, Level 16, State 1, Line 1

 

 

Check Catalog Msg 3853, State 1: Attribute (object_id=1362819917) of row (object_id=1362819917,parameter_id=1) in sys.parameters does not have a matching row (object_id=1362819917) in sys.objects.

GRANT VIEW DEFINITION ON  YOUR_VIEW TO USERANEM;

 

Msg 8992, Level 16, State 1, Line 1

 

可以通过下面三种方式获取定义脚本,如下所示

Check Catalog Msg 3853, State 1: Attribute (object_id=1362819917) of row (object_id=1362819917,parameter_id=2) in sys.parameters does not have a matching row (object_id=1362819917) in sys.objects.

.....................................................

 

CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.

 

USE YourSQLDba;  

GO  

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound  

FROM sys.sql_modules  

WHERE object_id = OBJECT_ID('PerfMon.SessionInfo');   

GO  

 

 

 

 

 

USE YourSQLDba;  

GO 

SELECT OBJECT_DEFINITION (OBJECT_ID('PerfMon.SessionInfo')) AS ObjectDefinition;   

GO  

 

 

 

 

USE YourSQLDba;  

GO 

EXEC sp_helptext 'PerfMon.SessionInfo'; 

CHECKDB found 0 allocation errors and 2 consistency errors in database 'TEST'.

 

 

 

 

 

 

 

 

 

如果是批量授权,那么可以使用下面脚本生成授权脚本。然后执行生成的脚本:

那么上面方式可以查看系统视图的定义吗? 实验验证测试一下即可知道。

图片 1

 

 

 

 

1:首先,在SSMS的“对象资源管理器”中是是无法查看系统视图的定义的。SSMS直接屏蔽了相关功能。

 

USE DatabaseName;

GO

---给用户授予查看存储过程定义的权限

 

DECLARE @loginname VARCHAR(32);

 

SET @loginname='[eopms_reader]'

 

SELECT  'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id)   '.'

          QUOTENAME(name)   ' TO '   @loginname   ';'

FROM    sys.procedures; 

 

 

--给用户授予查看自定义函数定义的权限

 

SELECT  'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id)   '.'

          QUOTENAME(name)   ' TO '   @loginname   ';'

FROM    sys.objects

WHERE   type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',

                       'AGGREGATE_FUNCTION' );

 

 

--给用户授予查看视图定义的权限

SELECT  'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id)   '.'

          QUOTENAME(name)   ' TO '   @loginname   ';'

FROM    sys.views;

 

 

--给用户授予查看视表定义的权限

SELECT 'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id) 

        QUOTENAME(name)   ' TO '   @loginname   ';' 

FROM sys.tables;

 

 

 

2:上面三种脚本方式,sys.sql_modules 无法查看系统视图定义,内置函数OBJECT_DEFINITION、系统存储过程OBJECT_DEFINITION可以查看系统视图的定义。

 

 

 

那么我们先找到系统视图sys.parameters的数据来源于那个系统基础表(System Base-Table Metadata),如下脚本所示,我们可以找到sys.parameters 最终来源于sys.syscolpars和 sys.sysobjvalues(关于如何获取系统视图定义,此处不做展开分析)

如果你想直接执行脚本,不想生成授权脚本,那么可以使用下面脚本实现授权。当然前提是你选择所要授权的数据库(USE DatabaseName)

    图片 2

 

 

 

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

CREATE VIEW sys.parameters AS

    SELECT object_id, name,

        parameter_id, system_type_id,

        user_type_id, max_length,

        precision, scale,

        is_output, is_cursor_ref,

        has_default_value, is_xml_document,

        default_value, xml_collection_id,

        is_readonly

    FROM sys.parameters$

    WHERE number = 1

 

GO

 

 

 

CREATE VIEW sys.parameters$ AS

    SELECT c.id AS object_id,

        c.number, c.name,

        c.colid AS parameter_id,

        c.xtype AS system_type_id,

        c.utype AS user_type_id,

        c.length AS max_length,

        c.prec AS precision,

        c.scale AS scale,

        sysconv(bit, c.status & 512) AS is_output,        -- CPM_OUTPUT

        sysconv(bit, c.status & 1024) AS is_cursor_ref,    -- CPM_CURSORREF

        sysconv(bit, isnull(v.objid, 0)) AS has_default_value,

        sysconv(bit, c.status & 2048) AS is_xml_document, -- CPM_XML_DOC        

        v.value AS default_value,

        xmlns AS xml_collection_id,

        sysconv(bit, c.status & 4194304) AS is_readonly -- CPM_IS_READONLY = 0x00400000

    FROM sys.syscolpars c

    LEFT JOIN sys.sysobjvalues v ON v.valclass = 9 AND v.objid = c.id AND v.subobjid = c.colid AND v.valnum = 0    -- SVC_PARAMDEFAULT

    WHERE number > 0 AND has_access('CO', c.id) = 1
DECLARE @loginname VARCHAR(32);

DECLARE @sqlcmd    NVARCHAR(MAX);

DECLARE @name  sysname;

DECLARE @schema_id  INT;

SET @loginname='[kerry]'

 

 

DECLARE procedure_cursor CURSOR  FORWARD_ONLY

FOR

    SELECT  schema_id, name

    FROM   sys.procedures;

 

 

OPEN procedure_cursor;

 

FETCH NEXT FROM procedure_cursor INTO @schema_id, @name;

 

 

 

---给用户授予查看存储过程定义的权限

 

WHILE @@FETCH_STATUS = 0

 

BEGIN

 

 

SET @sqlcmd= 'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(@schema_id)   '.'

          QUOTENAME(@name)   ' TO '   @loginname   ';'

 

--PRINT @sqlcmd;

EXEC sp_executesql @sqlcmd;

 

FETCH NEXT FROM procedure_cursor INTO @schema_id, @name;

 

END

 

CLOSE procedure_cursor;

 

DEALLOCATE procedure_cursor;

 

 

DECLARE function_cursor  CURSOR FAST_FORWARD 

FOR 

    SELECT schema_id, name

    FROM sys.objects

    WHERE type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',

                       'AGGREGATE_FUNCTION' );

 

 

--给用户授予查看自定义函数定义的权限

 

OPEN function_cursor;

 

FETCH NEXT FROM function_cursor INTO @schema_id,@name;

 

 

WHILE @@FETCH_STATUS = 0 

BEGIN

 

 

SET  @sqlcmd= 'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(@schema_id)   '.'

          QUOTENAME(@name)   ' TO '   @loginname   ';'

 

--PRINT @sqlcmd;

EXEC sp_executesql @sqlcmd;

 

FETCH NEXT FROM  function_cursor INTO  @schema_id, @name;

 

END

 

CLOSE function_cursor;

DEALLOCATE function_cursor;

 

 

DECLARE view_cursor CURSOR FAST_FORWARD

FOR

    SELECT schema_id, name FROM sys.views;

 

 

OPEN view_cursor;

 

FETCH NEXT FROM view_cursor INTO @schema_id, @name;

 

WHILE @@FETCH_STATUS = 0 

BEGIN

 

 

--给用户授予查看视图定义的权限

SET @sqlcmd= 'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(@schema_id)   '.'

          QUOTENAME(@name)   ' TO '   @loginname   ';'

 

--PRINT @sqlcmd;

EXEC sys.sp_executesql @sqlcmd;

 

FETCH NEXT FROM view_cursor INTO @schema_id, @name;

 

END

 

CLOSE view_cursor;

DEALLOCATE view_cursor;

 

 

 

DECLARE table_cursor CURSOR FAST_FORWARD

FOR    

 

    SELECT schema_id,name FROM sys.tables;

 

 

OPEN table_cursor;

FETCH NEXT FROM table_cursor INTO @schema_id, @name;

 

WHILE @@FETCH_STATUS = 0

BEGIN

 

    SET @sqlcmd ='GRANT VIEW DEFINITION ON '   SCHEMA_NAME(@schema_id)   '.'

              QUOTENAME(@name)   ' TO '   @loginname   ';'

    

    --PRINT @sqlcmd;

    EXEC sys.sp_executesql @sqlcmd;

 

    FETCH NEXT FROM table_cursor INTO @schema_id, @name;

 

END

 

CLOSE table_cursor;

DEALLOCATE table_cursor;

 

 

 

系统视图无法查看对应的系统定义是因为条件限制原因(has_access('CO', o.id) = 1),如下所示:

 

 

 

但是系统基础表sys.syscolpars和sys.sysobjvalues在正常情况下是不可见的。只有在数据库专用管理员连接方式(DAC Dedicated Administrator Connection)连接下才能可见。如下所示,可以判断数据来源于sys.syscolpars系统基础表。

如果单个用户授权,那么使用上面方法已经OK,但是一个系统,经常遇到这种授权情况,那么使用上面方法也会变得非常繁琐;另外,当新增表、视图、存储过程后,又需要修改相关授权脚本,这样变得非常麻烦繁琐,那么可以创建一个数据库角色,授予查看数据库对象定义的权限,然后新建登录名时,将这个数据库角色授予新建的登录名即可。这样以后即使有权限啥变化,只需要调整数据库角色的权限即可,不需修改登录名。对于重复性的授权工作会变得非常方便、简单。例如,需要给新建的登录名kerry授予查看数据库对象定义的权限,那么首先创建数据库角色View_Definiton,然后将查看定义的权限授予数据库角色View_Definiton,最后,创建了登录名kerry后,只需将角色View_Definiton授予kerry即可。

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

CREATE VIEW sys.sql_modules AS

    SELECT object_id = o.id,

        definition = object_definition(o.id),

        uses_ansi_nulls = sysconv(bit, o.status & 0x40000),             -- OBJMOD_ANSINULLS

        uses_quoted_identifier = sysconv(bit, o.status & 0x80000),      -- OBJMOD_QUOTEDIDENT

        is_schema_bound = sysconv(bit, o.status & 0x20000),             -- OBJMOD_SCHEMABOUND

        uses_database_collation = sysconv(bit, o.status & 0x100000),    -- OBJMOD_USESDBCOLL

        is_recompiled = sysconv(bit, o.status & 0x400000),              -- OBJMOD_NOCACHE

        null_on_null_input = sysconv(bit, o.status & 0x200000),         -- OBJMOD_NULLONNULL

        execute_as_principal_id = x.indepid,

        uses_native_compilation = sysconv(bit, case when (o.type = 'P') then o.status & 0x00000200 else 0 end)    -- OBJPRC_HEKATON

    FROM sys.sysschobjs o

    LEFT JOIN sys.syssingleobjrefs x ON x.depid = o.id AND x.class = 22 AND x.depsubid = 0 -- SRC_OBJEXECASOWNER

    WHERE o.pclass <> 100 -- x_eunc_Server

        AND ((o.type = 'TR' AND has_access('TR', o.id, o.pid, o.nsclass) = 1)

            OR (type IN ('P','V','FN','IF','TF','RF','IS') AND has_access('CO', o.id) = 1)

            OR (type IN ('R','D') AND o.pid = 0))

        AND (o.status2 & 0x00000020) = 0

 

GO

 

 

 

 

 

   

图片 3

USE AdventureWorks2014;

GO

CREATE ROLE [View_Definiton] AUTHORIZATION [dbo]

GO

 

 

 

 

 

 

DECLARE @loginname VARCHAR(32);

 

SET @loginname='View_Definiton'

 

SELECT  'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id)   '.'

          QUOTENAME(name)   ' TO '   @loginname   ';'

FROM    sys.procedures; 

 

 

--给用户授予查看自定义函数定义的权限

 

SELECT  'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id)   '.'

          QUOTENAME(name)   ' TO '   @loginname   ';'

FROM    sys.objects

WHERE   type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',

                       'AGGREGATE_FUNCTION' );

 

 

--给用户授予查看视图定义的权限

SELECT  'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id)   '.'

          QUOTENAME(name)   ' TO '   @loginname   ';'

FROM    sys.views;

 

 

--给用户授予查看视表定义的权限

SELECT 'GRANT VIEW DEFINITION ON '   SCHEMA_NAME(schema_id)  '.'

        QUOTENAME(name)   ' TO '   @loginname   ';' 

FROM sys.tables;

    图片 4

 

图片 5

 

 

 

 

此时即使在专用管理员连接下面也是无法删除这些数据的,会报“Ad hoc update to system catalogs is not supported”,对应中文提示为“不支持对系统目录进行即席更新”。如下所示:

 

   

 

ALTER ROLE [View_Definiton] ADD MEMBER [kerry]

    图片 6

 

GO

 

EXEC sp_configure 'allow_updates', 1;

 

RECONFIGURE WITH OVERRIDE;

如果你到这一步以为就结束了的话,那么你太天真了。内置函数OBJECT_DEFINITION、系统存储过程OBJECT_DEFINITION对于有些视图也束手无策。如下所示,获取系统视图sys.parameters的定义如下。

GO

CREATE VIEW sys.parameters

AS

    SELECT  object_id ,

            name ,

            parameter_id ,

            system_type_id ,

            user_type_id ,

            max_length ,

            PRECISION ,

            scale ,

            is_output ,

            is_cursor_ref ,

            has_default_value ,

            is_xml_document ,

            default_value ,

            xml_collection_id ,

            is_readonly ,

            is_nullable

    FROM    sys.parameters$

    WHERE   number = 1

USE TEST;

 

GO

 

DELETE FROM  sys.syscolpars WHERE id=1362819917;

其实对象sys.parameters$也是一个视图,正常情况下是无法查看sys.parameters$这个对象的,在专用管理员模式(DAC)下面才可以查看sys.parameters$的定义,而且只能通过系统内置函数OBJECT_DEFINITION,而sp_helptext 是会报错的。如果你要弄清楚一些系统视图的定义,那么基本上就要借助专用管理员模式(DAC)模式来查看。

GO

 

 

 

 

USE YourSQLDba; 

图片 7

GO

 

SELECT OBJECT_DEFINITION (OBJECT_ID('sys.parameters$')) AS ObjectDefinition;  

 

GO 

 

 

那么难道就没有办法解决这种问题了吗? 答案是当然有,不过,这种方式是没有官方文档而且也不被官方Support的,如果你要按下面方法操作,是有一定风险的。所以如果你决定按照下面方式修复元数据损坏的话,先做好备份。以防万一。

CREATE VIEW sys.parameters$ AS

    SELECT c.id AS object_id,

        c.number, c.name,

        c.colid AS parameter_id,

        c.xtype AS system_type_id,

        c.utype AS user_type_id,

        c.length AS max_length,

        c.prec AS precision,

        c.scale AS scale,

        sysconv(bit, c.status & 512) AS is_output,        -- CPM_OUTPUT

        sysconv(bit, c.status & 1024) AS is_cursor_ref,    -- CPM_CURSORREF

        sysconv(bit, isnull(v.objid, 0)) AS has_default_value,

        sysconv(bit, c.status & 2048) AS is_xml_document, -- CPM_XML_DOC        

        v.value AS default_value,

        xmlns AS xml_collection_id,

        sysconv(bit, c.status & 4194304) AS is_readonly, -- CPM_IS_READONLY = 0x00400000

        sysconv(bit, 1 - (c.status & 1)) AS is_nullable -- CPM_NOTNULL  = 0x00000001

    FROM sys.syscolpars c

    LEFT JOIN sys.sysobjvalues v ON v.valclass = 9 AND v.objid = c.id AND v.subobjid = c.colid AND v.valnum = 0    -- SVC_PARAMDEFAULT

    WHERE number > 0 AND has_access('CO', c.id) = 1

 

 

你必须将数据库实例在单用户模式下面启动,然后以专用管理员(DAC)连接到数据库,然后就可以删除基础表下面的数据了,如下截图所示:

其实在专用管理员模式(DAC)下面,虽然能查到sys.parameters$的定义,但是在基表sys.sysschobjs里面找不到sys.parameters$这个对象,其实你可以找到系统的Resource数据库

 

 

 

图片 8

C:Documents and Settings>net stop mssqlserver

 

The SQL Server (MSSQLSERVER) service is stopping.

一般位于<drive>:Program FilesMicrosoft SQL ServerMSSQL<version>.<instance_name>MSSQLBinn下面(以实际安装路径为准),名字为mssqlsystemresource.mdf ,你可以将其Copy到其它位置后,然后附加重命名为一个数据库,例如我附加为resource数据库,然后你就可以在SSMS界面直接获取系统视图的定义了。

The SQL Server (MSSQLSERVER) service was stopped successfully.

 

 

注意,在SQL Server 2014测试发现,SQL Server里面看不到System Tables下的相关基表了,应该是其屏蔽了这些功能,下面截图是在SQL Server 2008 R2测试环境。

 

 

C:Documents and Settings>net start mssqlserver /m"Microsoft SQL Serve

 

r Management Studio - Query"

 

The SQL Server (MSSQLSERVER) service is starting.

 

The SQL Server (MSSQLSERVER) service was started successfully.

图片 9

 

 

 

 

USE TEST;

GO

DELETE FROM  sys.syscolpars WHERE id=1362819917;

GO

 

----------------------------------------------------------------------------------

Warning: System table ID 41 has been updated directly in database ID 5 and cache coherence may not have been maintained. SQL Server should be restarted.

 

(2 row(s) affected)

 

 

参考资料:

图片 10

 

 

 

 

此时再去检查数据库一致性,你就会看到上面遇到的元数据损坏错误不见了。如下截图所示:

 

 

 

图片 11

 

 

其实如果是从SQL Server 2000还原的话,在SQL Server 2000当中是可以修改相关系统表的,如果执行了DBCC CHECKDB命令发现了元数据问题,那么可以直接修改系统表解决问题(当然只是部分情况),如果已经还原到了SQL Server 2008 以上数据库时,就必须按这种方式折腾,由于这种方式,官方是不支持的。所以还是有一定风险的。因为你不清楚潜在的风险,也不能确保任何场景都能解决问题而不出现意外情况。所以操作之前,尽量多测试、做好备份以防万一。

 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Server查看视图定义总结,Server中付与顾客查看对

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。