SqlDependency - getting the permissions working...

After much banging my head against the internet here is a canonical example of how to configure SQL Server permissions, roles to get SqlDependency working in a .Net app.

This is for sceneario where the identity running the .Net code is running as a configured user account with the db_datareader & db_datawriter.

-- Create Schema User
CREATE USER sql_dependency_schema_owner WITHOUT LOGIN;
GO

-- Create Schema for SqlDependency objects
CREATE SCHEMA sql_dependency AUTHORIZATION sql_dependency_schema_owner;
GO

-- Create Role for users of [sql_dependency]
CREATE ROLE sql_dependency_user;
GO

-- Grant role permissions
GRANT CONTROL ON SCHEMA::sql_dependency TO sql_dependency_user;
GRANT IMPERSONATE ON USER::sql_dependency_schema_owner TO sql_dependency_user;
GRANT CREATE PROCEDURE TO sql_dependency_user;
GRANT CREATE QUEUE TO sql_dependency_user;
GRANT CREATE SERVICE TO sql_dependency_user;
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO sql_dependency_user;
GRANT VIEW DEFINITION TO sql_dependency_user;
GRANT SELECT to sql_dependency_user;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sql_dependency_user;
GRANT RECEIVE ON QueryNotificationErrorsQueue TO sql_dependency_user;
GO

-- Set [sql_dependency] as user default schema add to role, example user 'ollie'
ALTER USER ollie WITH DEFAULT_SCHEMA = sql_dependency;
ALTER ROLE sql_dependency_user ADD MEMBER ollie;
GO

Comments

Showing a message box from a ViewModel in MVVM

I was doing a code review with a client last week for a WPF app using MVVM and they asked ' How can I show a message from the ViewModel? '. What follows is how I would (and have) solved the problem in the past. When I hear the words ' show a message... ' I instantly think you mean show a transient modal message box that requires the user input before continuing ' with something else ' - once the user has interacted with the message box it will disappear. The following solution only applies to this scenario. The first solution is the easiest but is very wrong from a separation perspective. It violates the ideas behind the Model-View-Controller pattern because it places View concerns inside the ViewModel - the ViewModel now knows about the type of the View and specifically it knows how to show a message box window: The second approach addresses this concern by introducing the idea of messaging\events between the ViewModel and the View. In the example below

Implementing a busy indicator using a visual overlay in MVVM

This is a technique we use at work to lock the UI whilst some long running process is happening - preventing the user clicking on stuff whilst it's retrieving or rendering data. Now we could have done this by launching a child dialog window but that feels rather out of date and clumsy, we wanted a more modern pattern similar to the way <div> overlays are done on the web. Imagine we have the following simple WPF app and when 'Click' is pressed a busy waiting overlay is shown for the duration entered into the text box. What I'm interested in here is not the actual UI element of the busy indicator but how I go about getting this to show & hide from when using MVVM. The actual UI elements are the standard Busy Indicator coming from the WPF Toolkit : The XAML behind this window is very simple, the important part is the ViewHost. As you can see the ViewHost uses a ContentPresenter element which is bound to the view model, IMainViewModel, it contains 3 child v

Custom AuthorizationHandler for SignalR Hubs

How to implement IAuthorizationRequirement for SignalR in Asp.Net Core v5.0 Been battling this for a couple of days, and eventually ended up raising an issue on Asp.Net Core gitHub to find the answer. Wanting to do some custom authorization on a SignalR Hub when the client makes a connection (Hub is created) and when an endpoint (Hub method) is called: I was assuming I could use the same Policy for both class & method attributes, but it ain't so - not because you can't, because you need the signatures to be different. Method implementation has a resource type of HubInnovationContext: I assumed class implementation would have a resource type of HubConnectionContext - client connects etc... This isn't the case, it's infact of type DefaultHttpContext . For me I don't even need that, it can be removed completely from the inheritence signature and override implementation. Only other thing to note, and this could be a biggy, is the ordering of the statements in th

Awkward Coder

Search This Blog