登录/用户帐户仅在设置为sysadmin服务器角色时才能执行存储过程

我正在通过IIS使用内置的NT AUTHORITY \ NETWORK SERVICE帐户(设置为ApplicationPool标识)来运行向网站提供数据的存储过程。我必须使用此帐户,因为它是目前唯一可用的帐户。

  • 我已经以数据库用户身份进行NT AUTHORITY \ NETWORK服务
    并给了它GRANT EXECUTE TO [NT AUTHORITY\NETWORK SERVICE] 可以执行它想要的任何东西。
  • User Mapping下进行登录,我 已授予它除sysadmin之外的所有服务器角色。
  • Securables下 我已授予它“管理员批量操作”,“连接任何数据库”, 连接SQL,查看任何数据库,查看服务器状态。

即使采用上述方法,我也无法使用该帐户在数据库中执行存储过程。它可以执行存储过程的唯一方法是,如果我转到SQL Server Instance > Security > Logins并勾选sysadmin下的Server Roles框。

我该怎么做才能使诸如NT AUTHORITY \ NETWORK SERVICE之类的帐户能够选择,插入,更新,删除,执行我的任何数据库中的所有对象?

更新1:来自execute as login = 'NT AUTHORITY\NETWORK SERVICE'; select user,* from sys.fn_my_permissions(null,'database'); revert的输出:

NT AUTHORITY\NETWORK SERVICE    database        CREATE TABLE
NT AUTHORITY\NETWORK SERVICE    database        CREATE VIEW
NT AUTHORITY\NETWORK SERVICE    database        CREATE PROCEDURE
NT AUTHORITY\NETWORK SERVICE    database        CREATE FUNCTION
NT AUTHORITY\NETWORK SERVICE    database        CREATE RULE
NT AUTHORITY\NETWORK SERVICE    database        CREATE DEFAULT
NT AUTHORITY\NETWORK SERVICE    database        BACKUP DATABASE
NT AUTHORITY\NETWORK SERVICE    database        BACKUP LOG
NT AUTHORITY\NETWORK SERVICE    database        CREATE TYPE
NT AUTHORITY\NETWORK SERVICE    database        CREATE ASSEMBLY
NT AUTHORITY\NETWORK SERVICE    database        CREATE XML SCHEMA COLLECTION
NT AUTHORITY\NETWORK SERVICE    database        CREATE SCHEMA
NT AUTHORITY\NETWORK SERVICE    database        CREATE SYNONYM
NT AUTHORITY\NETWORK SERVICE    database        CREATE AGGREGATE
NT AUTHORITY\NETWORK SERVICE    database        CREATE ROLE
NT AUTHORITY\NETWORK SERVICE    database        CREATE MESSAGE TYPE
NT AUTHORITY\NETWORK SERVICE    database        CREATE SERVICE
NT AUTHORITY\NETWORK SERVICE    database        CREATE CONTRact
NT AUTHORITY\NETWORK SERVICE    database        CREATE REMOTE SERVICE BINDING
NT AUTHORITY\NETWORK SERVICE    database        CREATE ROUTE
NT AUTHORITY\NETWORK SERVICE    database        CREATE QUEUE
NT AUTHORITY\NETWORK SERVICE    database        CREATE SYMMETRIC KEY
NT AUTHORITY\NETWORK SERVICE    database        CREATE ASYMMETRIC KEY
NT AUTHORITY\NETWORK SERVICE    database        CREATE EXTERNAL libraRY
NT AUTHORITY\NETWORK SERVICE    database        CREATE FULLTEXT CATALOG
NT AUTHORITY\NETWORK SERVICE    database        CREATE CERTIFICATE
NT AUTHORITY\NETWORK SERVICE    database        CREATE DATABASE DDL EVENT NOTIFICATION
NT AUTHORITY\NETWORK SERVICE    database        CONNECT
NT AUTHORITY\NETWORK SERVICE    database        CONNECT REPLICATION
NT AUTHORITY\NETWORK SERVICE    database        CHECKPOINT
NT AUTHORITY\NETWORK SERVICE    database        SUBSCRIBE QUERY NOTIFICATIONS
NT AUTHORITY\NETWORK SERVICE    database        AUTHENTICATE
NT AUTHORITY\NETWORK SERVICE    database        SHOWPLAN
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY USER
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY ROLE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY APPLICATION ROLE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY COLUMN ENCRYPTION KEY
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY COLUMN MASTER KEY
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY SCHEMA
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY ASSEMBLY
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY DATABASE SCOPED CONFIGURATION
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY DATASPACE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY EXTERNAL DATA SOURCE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY EXTERNAL FILE FORMAT
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY EXTERNAL libraRY
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY MESSAGE TYPE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY CONTRact
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY SERVICE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY REMOTE SERVICE BINDING
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY ROUTE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY FULLTEXT CATALOG
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY SYMMETRIC KEY
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY ASYMMETRIC KEY
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY CERTIFICATE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY SECURITY POLICY
NT AUTHORITY\NETWORK SERVICE    database        SELECT
NT AUTHORITY\NETWORK SERVICE    database        INSERT
NT AUTHORITY\NETWORK SERVICE    database        UPDATE
NT AUTHORITY\NETWORK SERVICE    database        DELETE
NT AUTHORITY\NETWORK SERVICE    database        REFERENCES
NT AUTHORITY\NETWORK SERVICE    database        EXECUTE
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY DATABASE DDL TRIGGER
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY DATABASE EVENT NOTIFICATION
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY DATABASE AUDIT
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY DATABASE EVENT SESSION
NT AUTHORITY\NETWORK SERVICE    database        KILL DATABASE CONNECTION
NT AUTHORITY\NETWORK SERVICE    database        VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
NT AUTHORITY\NETWORK SERVICE    database        VIEW ANY COLUMN MASTER KEY DEFINITION
NT AUTHORITY\NETWORK SERVICE    database        VIEW DATABASE STATE
NT AUTHORITY\NETWORK SERVICE    database        VIEW DEFINITION
NT AUTHORITY\NETWORK SERVICE    database        TAKE OWNERSHIP
NT AUTHORITY\NETWORK SERVICE    database        ALTER
NT AUTHORITY\NETWORK SERVICE    database        ALTER ANY MASK
NT AUTHORITY\NETWORK SERVICE    database        UnmASK
NT AUTHORITY\NETWORK SERVICE    database        EXECUTE ANY EXTERNAL SCRIPT
NT AUTHORITY\NETWORK SERVICE    database        ADMINISTER DATABASE BULK OPERATIONS
NT AUTHORITY\NETWORK SERVICE    database        CONTROL
wwl776 回答:登录/用户帐户仅在设置为sysadmin服务器角色时才能执行存储过程

  

我怎样才能给该用户选择,更新,插入,删除和   在服务器上所有当前和将来的数据库上执行?

您应将user添加到数据库角色db_datareaderdb_datawriter中,并在每个现有数据库中将其授予EXECUTE

对于将来的数据库,您可以将其映射到model数据库中并执行上述操作。对于已恢复的db_datareader以外的任何新创建的db_datawriter,此用户将在所有数据库上以EXECUTEdatabase的身份添加为databases的用户。

本文链接:https://www.f2er.com/3066395.html

大家都在问