I am using the built-in NT AUTHORITY\NETWORK SERVICE account through IIS (set as the ApplicationPool identity) to run stored procedures which provides data to the website. I have to use this account because it is the only account available to me at the moment.
GRANT EXECUTE TO [NT AUTHORITY\NETWORK SERVICE] so it
can execute whatever it wants. User Mapping for the login, I
have granted it all server roles except sysadmin. Securables
I have granted it Admin Bulk Operations, Connect Any Database,
Connect SQL, View Any Database, View Server State.Even with the above I cannot execute stored procedures in my database using that account. The ONLY way it can execute stored procedures is if I go to SQL Server Instance > Security > Logins and tick the sysadmin box under Server Roles.
What do I need to do to enable an account like NT AUTHORITY\NETWORK SERVICE to be able to select, insert, update, delete, execute etc all objects in any of my databases?
Update 1: Output from 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
How could I just give this user select, update, insert, delete, and execute on all current and future databases on the server?
You should add your user to database roles db_datareader, db_datawriter and grant it EXECUTE in every existing database.
For future databases you can map it into model database and do the above actions. This will add this user as db_datareader and db_datawriter with EXECUTE on all database for any newly created database except for restored databases.