sp_setapprole 和創建使用者/登錄

我們已經設置了 SQL Server 和我們的應用程序，使得 SQL Server 登錄具有……在使用任何工具訪問數據庫時沒有權限，除了我們的應用程序。這是必需的，因為有些人可能會啟動訪問（或 SQL Server Management Studio）並能夠以這種方式操作數據。

因此，我們設置了一個應用程序角色並賦予該應用程序角色必要的權限。一切執行良好，使用者無法訪問我們的表，除非他們使用我們的應用程序。

現在問題來了。在我們的應用程序中，應該可以創建額外的登錄名/使用者，或者將一些特定的使用者設置為管理員（只有管理員可以做與使用者相關的事情）。我不是 100% 確定，但在這種情況下，我認為使用者需要 SecurityAdmin 角色。所以我們的應用程序中有一些程式碼，如下所示：

IF NOT EXISTS ( SELECT  name
               FROM    sys.server_principals
               WHERE   name = @LoginName )
   BEGIN
       SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
           + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
           + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
       EXECUTE(@SQL);
   END

IF NOT EXISTS ( SELECT  name
               FROM    sys.database_principals
               WHERE   name = @LoginName )
   BEGIN
       SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
           + @LoginName + ']';
       EXECUTE(@SQL);
   END

IF EXISTS ( SELECT  name
           FROM    sys.server_principals
           WHERE   name = @LoginName )
   BEGIN
       EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
   END

問題當然是 server_principals 在主數據庫中，我無法為我的應用程序角色授予這些權限。有沒有辦法解決這個問題或解決這個問題的簡單方法？

嗯……實際上我的問題是我需要一個很好的例子來說明如何設置它。現在我認為我可以使用 AppUser 應用程序角色設置所有內容，並為該角色賦予應用程序的所有插入/選擇/更新/執行權限。

但是我應該給 AppAdmin 什麼權限，以便允許它在 SQL Server 上創建登錄和使用者，並允許他們將其他人設置為管理員並更改其他使用者的密碼。對此有任何建議，因為這涉及主數據庫中的一些東西，這超出了我的應用程序角色的範圍。還是我錯過了什麼？

使用我目前的設置，我得到：Msg 15151，Level 16，State 1，Line 1 無法更改登錄名“test.gebruiker”，因為它不存在或者您沒有權限。

正確地做到這一點並不是很簡單，雖然一旦設置，你就完成了。

數據使用者或應用程序角色預設具有數據庫範圍權限。數據庫使用者連結到登錄名，因此可以從他們的登錄名中獲得伺服器範圍的權限。應用程序角色未連結，只能通過使用一種模擬形式獲得伺服器範圍的權限。

預設情況下，通過模擬來授予這些伺服器範圍權限的任何方式都將失敗。這是設計使然。

您有 2 個選項：

1. 告訴 SQL Server，您作為 DBA 信任該數據庫中的任何使用者，如果使用模擬，則可以將權限升級到伺服器範圍的權限。您可以通過將選項設置TRUSTWORTHY為ON 但是如果您不完全理解此選項首先閱讀的內容來完成此操作： SQL Server 2012 最佳實踐白皮書（特別是第 18 頁和第 19 頁關於**“數據庫所有權和信任”**）
2. 使用簽名的儲存過程來完成您的任務。

我先給你一個選項1的例子：

CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO

EXEC sp_addsrvrolemember    @loginame='appRoleProxy',
                           @rolename='securityadmin'

GO

CREATE DATABASE [Stefaan]
go

ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO

USE [Stefaan]
GO

--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO

CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO

CREATE PROCEDURE dbo.usp_testproc 
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
   IF NOT EXISTS ( SELECT  name
                   FROM    sys.server_principals
                   WHERE   name = @LoginName )
       BEGIN
           SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
               + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
               + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
           EXECUTE(@SQL);
       END

   IF NOT EXISTS ( SELECT  name
                   FROM    sys.database_principals
                   WHERE   name = @LoginName )
       BEGIN
           SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
               + @LoginName + ']';
           EXECUTE(@SQL);
       END

   IF EXISTS ( SELECT  name
               FROM    sys.server_principals
               WHERE   name = @LoginName )
       BEGIN
           EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
       END
END
GO

GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO


sp_setapprole   @rolename='appRole',
               @password='Passw0rd!'



EXEC usp_testproc 'testlogin','password23@','stefaan'

現在，對於選項 2的範例，我想指出另一個問題的答案： Msg 4834“您無權使用批量載入語句”

引用自：https://dba.stackexchange.com/questions/57858