时间戳

首页 > 解决方案 > 登录/用户帐户只有在设置为 sysadmin 服务器角色时才能执行存储过程

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

问题描述

我通过 IIS 使用内置的 NT AUTHORITY\NETWORK SERVICE 帐户（设置为 ApplicationPool 身份）来运行向网站提供数据的存储过程。我必须使用这个帐户，因为它是我目前唯一可用的帐户。

即使有上述情况，我也无法使用该帐户在我的数据库中执行存储过程。它可以执行存储过程的唯一方法是，如果我转到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

标签： sql-serversql-server-2017

解决方案

我怎么能让这个用户在服务器上所有当前和未来的数据库上选择、更新、插入、删除和执行？

您应该将您的角色添加user到数据库角色db_datareader，并在每个现有数据库中db_datawriter授予它。EXECUTE

对于未来的数据库，您可以将其映射到model数据库并执行上述操作。这将在所有新创建的数据库上添加此用户作为db_datareader和db_datawriterwith ，但 restore 除外。EXECUTEdatabasedatabases

推荐阅读