How to: Grant Database Permissions to an Instance of Notification Services
This procedure shows how to grant database permissions to the account used by a Notification Services engine, which is typically a NS$instanceName Microsoft Windows service, to access SQL Server. The account used can be either a Windows account or a SQL Server Login account. This procedure assumes that the account has already been granted permissions to log in to SQL Server.
|The default instance database name is instanceNameNSMain. The default application database name is instanceName + ApplicationName. However, in SQL Server 2005, you can specify the database name in the instance configuration and application definition.|
In SQL Server Management Studio Object Explorer, expand Databases.
For the instance database and each application database, do the following:
Expand the database.
Right-click the Security folder, point to New, and then select User.
In the User name box, enter a name for the database user.
In the Login name box, enter the name of the login.
The login name must exactly match the name of an existing login in the instance of SQL Server. Click Search to look for the login name.
In the Database role membership box, select the appropriate database role.
If the engine runs a hosted event provider, select the NSEventProvider role. If the engine runs a generator, select NSGenerator. If the instance runs a distributor, select NSDistributor. If the engine runs all components, select the NSRunService role.
Important Grant the minimum permissions required by the account. For example, if the account is used only to submit events, add the account to the NSEventProvider database role, but not the other database roles.
- Expand the database.
The following code shows how to grant database permissions to an instance of Notification Services and its application. The same account runs hosted event providers, distributors, and the application's generator, so the account is added to the NSRunService database role:
USE [InstanceNameNSMain]; CREATE USER [MyLogin]; EXEC sp_addrolemember N'NSRunService', N'MyLogin'; USE [InstanceNameAppName]; CREATE USER [MyLogin]; EXEC sp_addrolemember N'NSRunService', N'MyLogin';