您目前已離線,請等候您的網際網路重新連線

不支援您的瀏覽器

您必須更新瀏覽器,才能使用此網站。

更新至最新版本的 Internet Explorer

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

徵狀
如果以應用程式角色為基礎的應用程式嘗試從 Microsoft SQL Server 2005 主資料庫的任一系統資料表中選取所有記錄,您可能會遇到下列其中一種徵狀:
  • 沒有傳回記錄。
  • 收到下列錯誤訊息:
    沒有權限。
例如,如果應用程式使用下面的查詢,可能就會發生這個問題。
select * from master.dbo.syslogins
發生的原因
以應用程式角色為基礎的應用程式是設計用來搭配資料庫中的特定資訊使用。這些應用程式無法存取主檢視或動態管理檢視中的系統資料表,因為這些檢視包含有伺服器層級的資訊。
解決方案
如果要解決這個問題,請使用經過憑證簽署的程序來存取伺服器層級的系統資料表。經過憑證簽署的程序有下列優點:
  • 不需要使用追蹤旗標。
  • 所透露的伺服器層級資訊較少。以應用程式角色為基礎的應用程式必須使用預存程序,而非使用一般的查詢。預存程序比較可能只傳回應用程式所需要的特定資料。
其他可行方案
如果要解決這個問題,請啟用全域追蹤旗標 4616。
其他相關資訊
下面的程式碼範例是經過憑證簽署的程序範例。
USE masterGOCREATE DATABASE approle_db ;GOCREATE LOGIN some_login WITH PASSWORD = 'SomePa$$word!' ;GOUSE approle_dbGOCREATE USER some_user FOR LOGIN some_loginGOEXEC 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!' ;GOUSE masterGOGRANT VIEW ANY DEFINITION TO execute_as_login ;GRANT VIEW SERVER STATE   TO execute_as_login ;GOUSE approle_dbGOCREATE 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 ;GOGRANT EXECUTE ON access_server_system_tables TO an_approle ;GOCREATE CERTIFICATE signing_cert ENCRYPTION BY PASSWORD = 'SomeCertPa$$word'    WITH SUBJECT  = 'Signing Cert' ;GOBACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer' ;GOADD 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 masterGOCREATE 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 ;GOGRANT AUTHENTICATE SERVER TO signing_cert_loginGO----------------------------------------------------------------------- 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 masterGODROP DATABASE approle_db ;GODROP LOGIN some_login;GODROP LOGIN execute_as_login;GODROP LOGIN signing_cert_login ;GODROP 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 ;GORECONFIGURE ;GOEXEC sp_configure 'xp_cmdshell', 1 ;GORECONFIGURE ;GOEXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\signing_cert.cer"' ;GOEXEC sp_configure 'xp_cmdshell', 0 ;GORECONFIGURE ;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_dbGOEXEC sp_setapprole 'an_approle', 'SomeAppRolePa$$word!'GOEXEC access_server_system_tablesGO
內容

文章識別碼:906549 - 最後檢閱時間:11/20/2007 17:44:00 - 修訂: 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
意見反應
e="text/javascript" src="https://c.microsoft.com/ms.js">