Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save astrohart/3a392d7559603eb86138dc01f009d5f8 to your computer and use it in GitHub Desktop.
Save astrohart/3a392d7559603eb86138dc01f009d5f8 to your computer and use it in GitHub Desktop.
Open NETWORK SERVICE full perms on Microsoft SQL Serve 2019 database.
/*
Script to Open Full Permissions on a Database for a Windows Service
Purpose: Open full access privileges to the NT AUTHORITY\NETWORK SERVICE
user on the local computer to the SQL Server database named MyDatabase.
Objective is to allow Windows Service processes that run under that user
to access the target dataase hosted by an instance of Microsoft SQL Server
2019.
SQL Server version: Microsoft SQL Server 2019
Brian C. Hart, Ph.D. hereby disclaims all copyright interest in the
following SQL Server query.
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <https://www.gnu.org/licenses/>.
Features:
- After this script has been run on the database MyDatabase, then the
database can be connected by any Windows Service process that is
running under the user NT AUTHORITY\NETWORK SERVICE.
- Basically, if you write a .NET Windows Service that has network access,
and is configured to run under the NT AUTHORITY\NETWORK SERVICE user,
then this script should be run on your SQL Server database. Otherwise,
if the service process tries to open a connection to the SQL Server
instance that hosts MyDatabase, an exception will be thrown saying,
basically, that the service process lacks sufficient permissions to
access the database.
Directions for Use:
1. Open Microsoft SQL Server Management Studio 18.x+.
2. Connect to an instance of Microsoft SQL Server 2019.
3. Be sure that the instance to which you connect has the target
database attached to it.
4. Open a new Query window.
5. Connect the Query window opened in the previous step to the target
database, if you have not already done so.
6. Copy and paste all the text from this gist into the new Query Editor
window.
7. Everywhere you see the text MyDatabase, replace it with the name
of the target database (case-sensitive).
8. Run the query. It should (fingers crossed) execute successfully.
*/
use [MyDatabase]
GO
GRANT ALTER TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY APPLICATION ROLE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ASSEMBLY TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ASYMMETRIC KEY TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY CERTIFICATE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY CONTRACT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATABASE AUDIT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATABASE DDL TRIGGER TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATASPACE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY EXTERNAL DATA SOURCE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY EXTERNAL FILE FORMAT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY FULLTEXT CATALOG TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY MASK TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY MESSAGE TYPE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY REMOTE SERVICE BINDING TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ROLE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ROUTE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SCHEMA TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SECURITY POLICY TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SERVICE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SYMMETRIC KEY TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER ANY USER TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT AUTHENTICATE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT BACKUP DATABASE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT BACKUP LOG TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CHECKPOINT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CONNECT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CONNECT REPLICATION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CONTROL TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE AGGREGATE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE ASSEMBLY TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE ASYMMETRIC KEY TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE CERTIFICATE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE CONTRACT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE DEFAULT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE FULLTEXT CATALOG TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE FUNCTION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE MESSAGE TYPE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE PROCEDURE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE QUEUE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE REMOTE SERVICE BINDING TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE ROLE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE ROUTE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE RULE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE SCHEMA TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE SERVICE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE SYMMETRIC KEY TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE SYNONYM TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE TABLE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE TYPE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE VIEW TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT CREATE XML SCHEMA COLLECTION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT DELETE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT EXECUTE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT INSERT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT REFERENCES TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT SELECT TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT SHOWPLAN TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT TAKE OWNERSHIP TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT UNMASK TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT UPDATE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT VIEW DATABASE STATE TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT VIEW DEFINITION TO [NT AUTHORITY\NETWORK SERVICE]
GO
use [MyDatabase]
GO
GRANT ALTER TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY APPLICATION ROLE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ASSEMBLY TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ASYMMETRIC KEY TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY CERTIFICATE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY CONTRACT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATABASE AUDIT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATABASE DDL TRIGGER TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY DATASPACE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY EXTERNAL DATA SOURCE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY EXTERNAL FILE FORMAT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY FULLTEXT CATALOG TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY MASK TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY MESSAGE TYPE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY REMOTE SERVICE BINDING TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ROLE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY ROUTE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SCHEMA TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SECURITY POLICY TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SERVICE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY SYMMETRIC KEY TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT ALTER ANY USER TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT AUTHENTICATE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT BACKUP DATABASE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT BACKUP LOG TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CHECKPOINT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CONNECT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CONNECT REPLICATION TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CONTROL TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE AGGREGATE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE ASSEMBLY TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE ASYMMETRIC KEY TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE CERTIFICATE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE CONTRACT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE DEFAULT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE FULLTEXT CATALOG TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE FUNCTION TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE MESSAGE TYPE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE PROCEDURE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE QUEUE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE REMOTE SERVICE BINDING TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE ROLE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE ROUTE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE RULE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE SCHEMA TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE SERVICE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE SYMMETRIC KEY TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE SYNONYM TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE TABLE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE TYPE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE VIEW TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT CREATE XML SCHEMA COLLECTION TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT DELETE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT EXECUTE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT INSERT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT REFERENCES TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT SELECT TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT SHOWPLAN TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT TAKE OWNERSHIP TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT UNMASK TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT UPDATE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT VIEW DATABASE STATE TO [NT AUTHORITY\SYSTEM]
GO
use [MyDatabase]
GO
GRANT VIEW DEFINITION TO [NT AUTHORITY\SYSTEM]
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment