search

IntelliTect / IntelliTect.AspNetCore.SignalR.SqlServer

A Microsoft SQL Server backplane for ASP.NET Core SignalR.
Apache License 2.0
36 stars 5 forks source link

Document minimal SQL Server permissions #11

Open ascott18 opened 1 year ago

ascott18 commented 1 year ago
  1. Make a SQL Server login with zero permissions
  2. Start adding permissions until everything works.
  3. Write those permissions down somewhere.
andrew-tyler-watson commented 1 year ago

I have a set of permissions I have found that work.

  1. Create a Schema on the database (I use SignalR. Make sure to change the schema in your config if you use a different on)
  2. Make your user account the owner of this new schema and make it the default schema for that account
  3. Make that user a part of ddl_admin (create table permissions for the initial install, create stored procedure permissions for SqlDependency connections)
  4. 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.