Notification Services Database Roles
When you create an instance of Notification Services, it creates database roles in the instance and application databases. The instance's engine, subscription management interfaces, non-hosted event providers, as well as the administrative staff, use these roles to obtain necessary permissions in the databases.
Database Roles
All instance and application databases contain the same Notification Services database roles. The following table lists these roles and their permissions.
Note
Although database roles and their permissions can be changed by members of the sysadmin fixed server role or the db_owner or db_securityadmin database roles, Microsoft recommends not changing the permissions granted to these roles, because customized roles make determining effective permissions more difficult.
Role
Permissions
NSAnalysis
Can execute stored procedures that produce reports for performance analysis and troubleshooting.
Users in this role can read all tables in the database, which might be necessary for ad hoc reports.
This role also has NSReader permissions.
NSAdmin
Can enable and disable instances, applications, and components.
Members of the db_owner database role and the sysadmin and dbcreator fixed server roles can also enable and disable instances, applications, and components.
NSDistributor
Can execute stored procedures that perform SELECT and UPDATE operations on notification and distributor work tables.
Distributors require the permissions granted to this role. Add the account used by the Microsoft Windows service to this role when distributors are located on their own server. If distributors are located on the same server as hosted event providers and the generator, use the NSRunService role.
This role also has NSReader permissions.
NSEventProvider
Can execute stored procedures that perform INSERT operations on the event tables, and SELECT, INSERT, and UPDATE operations on the event batch tables.
Event providers require the permissions granted to this role. Non-hosted event provider accounts should always use this role. Hosted event providers should use this role if they are located on their own server. If hosted event providers are located on the same server as distributors and the generator, use the NSRunService role.
This role also has NSReader permissions.
NSGenerator
Can execute stored procedures used by the generator.
Generators require the permissions granted to this role. Add the account used by the Windows service to this role when a generated is located on its own server. If the generator is located on the same server as hosted event providers and distributors, use the NSRunService role.
Rules in the application definition file (ADF) are executed using the permissions of this role.
This role also has NSReader permissions.
NSReader
Can execute stored procedures that read instance and application metadata.
NSRunService
This role has the combined permissions of the NSEventProvider, NSGenerator, NSDistributor, NSReader, NSMonitor, and NSVacuumer roles. The NS$instance_name Windows service requires these permissions to evaluate subscriptions and generate notifications.
If hosted event providers, the generator, and distributors all run on one server, assign this role to the account used by the NS$instance_name Windows service to access SQL Server. Otherwise, assign appropriate permissions through the NSEventProvider, NSGenerator, and NSDistributor roles.
NSSubscriberAdmin
Can execute stored procedures that read, update, and delete rows from subscriber and subscription-related tables.
Subscription management applications require the permissions granted to this role. Add the account used by subscription management application to access SQL Server to this role in each instance and application database.
.gif "ms171396.note(en-US,SQL.90).gif")
Note:
Notification Services automatically cascades the deletion of a subscriber record to all related subscriptions, in all applications, even if the user deleting the subscriber record does not have access to the application databases. Subscription removal is performed using the permissions of the database owner.
This role also has NSReader permissions.
NSVacuum
Can execute stored procedures that remove obsolete data from the application databases. Add administrators who may need to run the NSVacuum stored procedure to this database role.
This role also has NSReader permissions.
To view the permissions assigned to these database roles, use the sp_helprotect system stored procedure.
See Also
Concepts
Permissions Required to Deploy and Administer Notification Services