Document minimal SQL Server permissions
Opened this issue · 1 comments
ascott18 commented
- Make a SQL Server login with zero permissions
- Start adding permissions until everything works.
- Write those permissions down somewhere.
andrew-tyler-watson commented
I have a set of permissions I have found that work.
- Create a Schema on the database (I use SignalR. Make sure to change the schema in your config if you use a different on)
- Make your user account the owner of this new schema and make it the default schema for that account
- Make that user a part of ddl_admin (create table permissions for the initial install, create stored procedure permissions for SqlDependency connections)
- Here are a list of permissions that we also ran for giving the user permissions to properly create objects needed for the SqlDependency to work
GRANT CREATE SERVICE TO [User]
GRANT CREATE QUEUE TO [User]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [User]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [User]
If you are getting an error that says "cannot find user 'owner'", you can fix that with steps 1 & 2. If you are messages indicating that the package is in polling loop mode but you did not specify that, it is possible you are missing permissions to properly start the SqlDepedency. The grants above may help with that.