當以應用程式角色為基礎的應用程式嘗試從 SQL Server 2005 主資料庫的任一系統資料表中選取記錄時,您可能會收到「沒有權限」錯誤訊息

文章翻譯 文章翻譯
文章編號: 906549 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

徵狀

如果以應用程式角色為基礎的應用程式嘗試從 Microsoft SQL Server 2005 主資料庫的任一系統資料表中選取所有記錄,您可能會遇到下列其中一種徵狀:
  • 沒有傳回記錄。
  • 收到下列錯誤訊息:
    沒有權限。
例如,如果應用程式使用下面的查詢,可能就會發生這個問題。
select * from master.dbo.syslogins

發生的原因

以應用程式角色為基礎的應用程式是設計用來搭配資料庫中的特定資訊使用。這些應用程式無法存取主檢視或動態管理檢視中的系統資料表,因為這些檢視包含有伺服器層級的資訊。

解決方案

如果要解決這個問題,請使用經過憑證簽署的程序來存取伺服器層級的系統資料表。經過憑證簽署的程序有下列優點:
  • 不需要使用追蹤旗標。
  • 所透露的伺服器層級資訊較少。以應用程式角色為基礎的應用程式必須使用預存程序,而非使用一般的查詢。預存程序比較可能只傳回應用程式所需要的特定資料。

其他可行方案

如果要解決這個問題,請啟用全域追蹤旗標 4616。

其他相關資訊

下面的程式碼範例是經過憑證簽署的程序範例。
USE master
GO

CREATE DATABASE approle_db ;
GO

CREATE LOGIN some_login WITH PASSWORD = 'SomePa$$word!' ;
GO

USE approle_db
GO

CREATE USER some_user FOR LOGIN some_login
GO

EXEC sp_addapprole 'an_approle', 'SomeAppRolePa$$word!' ;
GO

---------------------------------------------------------------------
-- This section shows how to use a certificate to authenticate
-- a signed procedure.
---------------------------------------------------------------------

CREATE LOGIN execute_as_login WITH PASSWORD = 'SomePa$$word!' ;
GO

USE master
GO

GRANT VIEW ANY DEFINITION TO execute_as_login ;
GRANT VIEW SERVER STATE   TO execute_as_login ;
GO

USE approle_db
GO

CREATE USER execute_as_user FOR LOGIN execute_as_login ;
GO

--
-- You must use EXECUTE AS 'authenticator' here because the application role
-- does not have a server identity. Therefore, the application role cannot use
-- the certificate permissions on the server.  Therefore, you
-- need a new execution context to which you can grant
-- the needed VIEW* permissions.
--
CREATE PROC access_server_system_tables
  WITH EXECUTE AS 'execute_as_user'
AS
  SELECT * FROM master.dbo.syslogins    ;
  SELECT * FROM master.dbo.sysprocesses ;
GO

GRANT EXECUTE ON access_server_system_tables TO an_approle ;
GO

CREATE CERTIFICATE signing_cert ENCRYPTION BY PASSWORD = 'SomeCertPa$$word'
    WITH SUBJECT  = 'Signing Cert' ;
GO

BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer' ;
GO

ADD SIGNATURE TO access_server_system_tables
    BY CERTIFICATE signing_cert WITH PASSWORD = 'SomeCertPa$$word' ;
GO

---------------------------------------------------------------------
-- We must create a copy of the signing certificate in the target
-- database. In this case, the target database is the master database.
-- This copy of the signing certificate can vouch
-- for the execution contexts that enter this database from the
-- signed procedure.
---------------------------------------------------------------------
USE master
GO

CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer' ;
GO

--
-- Because the VIEW* permissions in question are server-level permissions,
-- we need an AUTHENTICATE SERVER on a login-mapped certificate.
--
CREATE LOGIN signing_cert_login FROM CERTIFICATE signing_cert ;
GO

GRANT AUTHENTICATE SERVER TO signing_cert_login
GO


---------------------------------------------------------------------
-- Now you can open a new connection as "some_login" and
-- set the application role. Then, call the "access_server_system_tables"
-- procedure, and obtain verification that you can access server-level information
-- when the application role-based application runs.  




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


---------------------------------------------------------------------
-- Clean up after the procedure.

---------------------------------------------------------------------
USE master
GO

DROP DATABASE approle_db ;
GO

DROP LOGIN some_login;
GO

DROP LOGIN execute_as_login;
GO

DROP LOGIN signing_cert_login ;
GO

DROP CERTIFICATE signing_cert;
GO

--
-- Make sure to delete the certificate file. For example, delete
-- C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\signing_cert.cer
--
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'xp_cmdshell', 1 ;
GO
RECONFIGURE ;
GO

EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\signing_cert.cer"' ;
GO

EXEC sp_configure 'xp_cmdshell', 0 ;
GO
RECONFIGURE ;
GO


-- ============================================================================
-- - Application role access to server information - Demo usage.sql
--
--
--  This code is companion code that shows an example of application role access
--  to server information by using a certificate-signed procedure.
--
-- ============================================================================

--------------------------------------------------
-- Connect as some_login
--------------------------------------------------
USE approle_db
GO

EXEC sp_setapprole 'an_approle', 'SomeAppRolePa$$word!'
GO

EXEC access_server_system_tables
GO

屬性

文章編號: 906549 - 上次校閱: 2007年11月20日 - 版次: 2.2
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium Based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium Based Systems
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
關鍵字:?
kbtshoot kbprb kbsql2005engine KB906549
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com