Thursday, July 26, 2012

It's easy to google "The database principal owns a schema in the database, and cannot be dropped" error, but have you tried to google "The database principal owns a service in the database, and cannot be dropped."?

I tried, and the results were miserable -- I could find only half-baked solutions.
In my case I restored SQL Server backup database that had service broker turned on and then tried to drop orphaned user... unsuccessfully as you can guess.

Here's the solution.

1) Run this query:

select
'alter authorization on service::[' + name + '] to dbo;' as [SQL to execute prior to dropping user]
from sys.services
where principal_id = user_id('MyServer\MyOrphanedUser');

2) Execute SQL that you get as result of query above.
It would look something like that:

alter authorization on service::[SqlQueryNotificationService-25509d9f-4171-4701-817f-834f79a4a882] to dbo;

Thursday, May 19, 2011

The problem

My application has multiple features. For example:1) View resume.2) Sending message to resume poster.3) Viewing contact information on a resume.My application has multiple users. For example:1) Anonymous2) Fred Lurker3) Paul GenerousI want to allow some users to have access to one set of features and other users to have access to another set of features.For example I want:1) Anonymous users to have ability to view resume2) Fred Lurker to be able to view resume and to send messages to resume poster.3) Paul Generous to be able to see resume, send messages, and be able to see contact information on the resume.

Direct Permissions Mapping

The most direct approach would be to make every feature check a user who tries to access it. For example:

That’s very direct and simple, but does not scale at all. My application has thousands of users, new users are added to the system, old users are deleted, and existing users are getting access more or less features over time. In order to deal with all that code would have to constantly change which is not feasible.

Role-based Permissions

Better approach would be to introduce roles. For example:1) “Recruiters” role that has access to “Sending message to resume poster” feature.2) “PayingUsers” role that has access to “Viewing contact information on a resume” feature.Then:1) Add “Fred Lurker” user to “Recruiters” role.2) Add “Paul Generous” user to “PayingUsers” role.With such approach software developers would define what roles have access to what features. They would define it in code (e.g. in C#, C++ or Java).Application administrator would define roles that users have access to.When Paul Generous pays his membership fee, administrator would simply add Paul to “PayingUsers” role. Administrator does not need to explicitly define what features Paul would have access to, because it’s already defined by developers in application code for “PayingUsers” role.To summarize:1) Roles introduce one intermediate step in mapping users to features.2) Developers map roles to features3) Administrators map users to roles.

Enterprise Permissions

What if I want my application to be more flexible and allow administrator to map groups of users to features without asking developers to modify code?That could be setup like this:1) My enterprise system would still have Users.2) I’d add Groups, so administrator would be able to add users to groups.3) Developer would add Privileges, so administrator would be able to map groups to privileges.4) Developer would code features in such a way that one feature would be mapped to one privileges in my code:

If (user.HasPrivilege(“ViewResume”)){ ShowResume()}

5) Administrator would create and delete Groups and map these groups.Enterprise Permissions system gives lots of flexibility to application administrators. It’s very appealing for IT department management to be able to tweak users’ permissions without need to wait for developers releasing new version of the app. That’s why such enterprise permissions systems are so popular.

Disadvantages of Enterprise Permissions

Unfortunately in real life flexible enterprise permissions system causes nothing but pain.Here’s why:1) With any flexibility comes added complexity.Having “Users-Groups-Privileges-Features” chain instead of shorter “Users-Roles-Features” chain – significantly complicates the number of possible combinations of how permissions to access features can be granted to end users.That means permissions could be granted to a user in several different ways through multiple groups. So it’s hard to revoke user’s permissions if necessary simply because it’s hard to figure out what permissions does the user really have.2) It’s very hard for administrators to grasp what group should map to what privileges.Administrator focuses on the end user. Administrator knows what groups user should belong to. But administrators have only vague idea about what privilege allows user to do in the application. End result: developer is setting up the permissions anyway. 3) Enterprise permissions are much harder for developers.Developers know what features should be available to what role (see Role-based Permissions above). Developers can map features to roles in their code.Can developers map features to privileges and then map privileges to groups?Yes, they can. But it’s harder. It’s more work. It requires both coding in C#/Java and scripting in SQL. Or even worse approach – mapping groups to privileges in the UI (error-prone deployment nightmare).Administrators can setup new groups that developers do not know about. Administrators can delete groups that developers originally created. All that can quickly bring system to its knees. That’s why in the end administrators are afraid to create and delete groups and that defeats original purpose of enterprise system to give more flexibility to application administrators.4) It’s hard to trace changes in Enterprise Permissions.In role based system roles-features mapping is coded in C#/Java and is stored under source control. Code changes history helps to find out when and why this role was mapped to that feature.Not so with mapping between groups and privileges. Such mapping is stored in database and is wiped out without trace every time when administrator changes the mapping.There are no comments on why groups-features mapping was done the way it was done. There is simply no place to put such comment (unlike roles-features mapping that can be commented in C#/Java code).

Conclusion

The most robust way of handing permissions in most of applications is with role-based permissions (Users-Roles-Features).In spite of “flexibility” appeal of enterprise-based permissions (Users-Groups-Privileges-Features), such system has serious disadvantages and virtually no real advantages.

Tuesday, January 25, 2011

It's a tough problem to create HTML table in such a way that supports all of the following requirements simultaneously:1) Has scrollable table body with content.2) Has fixed table column headers.3) Has header columns automatically aligned with body columns.

It allowed me:1) Not to specify columns width explicitly.2) Have multi-row headers with some cells spanning across several columns. The only trick is that first header row should have the same number of cells as body row.

While it's the best solution I could find -- it's still not perfect.If table is pretty busy, and table content pushes column borders pretty hard -- header columns do not align perfectly with body columns.

Monday, October 18, 2010

Today I rolled out Boolean Search feature for PostJobFree.PostJobFree uses SQL Server backend, so it seems obvious to use Full-Text search that SQL Server have to process boolean search queries from users.Unfortunately it's not as easy:Full-Text search CONTAINS query is pretty strict, and crashes the whole SELECT statement if CONTAINS clause syntax is not correct. For example:

asp sql

query would crash SQL Server's SELECT command if the query would be passed into CONTAINS clause as-is. Correct syntax would be:

asp AND sql

So I need to write a parser that parses user's search input into boolean search query object hierarchy and then renders CONTAINS query for SQL Server from that hierarchy.Here's how I designed it.Search object hierarchy consist of the following parts:1) Word. It consists only of letters, digits, dots, and dashes. Dashes can be only be inside the word, between letters/digits. Dash outside the word is considered as negation.2) Phrase. Phrase can have any chars wrapped in quotes.3) OrList. OrList consists from two or more elements, separated by "or". For example:

("sql server" or oracle or DB2)

4) AndList. AndList consists from two or more elements, separated by "and" or "and not" operators. For example:

sql and asp and not oracle

This query would be rendered into AndList that consists of 3 elements.Both OrList and AndList could consist not only from atomic pieces (such as Word and Phrase), but also from OrList and AndList subelements.

Parsing logic:1) Split input query by quote char ('"'). Basically even elements in this split list are phrases. Everything else does not contain phrases.2) After phrases are extracted, partially processed list consists of strings and Phrase objects.3) Next step is to extract parenthesis that are not wrapped by other parentheses. Consider example:

(one and (two or three)) or four

"one and (two or three)" would be extracted from original query, and then recursively processed again.The recursion ends when there are no more parentheses.4) When recursion is so deep, that there are no parentheses left in a subquery, then that subquery is split by "or" word (or "|" char).5) Last major step is to split remaining subquery into AndList. The trick here would be to appropriately assign negation if subquery was preceded by negation orerator "not", "!", or leading dash ("-").

After query is fully parsed, it's time to get final CONTAINS query for SQL Server.It can be accomplished by overriding ToString() method for Word, Phrase, OrList, and AndList:1) Word just converting containing text to UpperCase.2) Phrase's ToString() overload converts InnerText to UpperCase and wraps it by quotes.3) AndList joins all elements into single string using "&" separator or "&!" separator if one of AndList's subqueries was negated.4) OrList joins all elements into single string using "|" separator. Then it wraps the result by parenthesis.

But what if you want to set it up for a control or just a function and do not have predefined list of pages to specify it in web.config?Of maybe you don't want to pollute web.config with junk like that?

There should be some way to do it in C# code, right?Right.Here's how you do it:

HttpContext.Current.Server.ScriptTimeout = 600; // 10 minutes

If that's what you were looking for, you probably want to test it.I tried to test it too, and it turned out to be tricky.

First I set web.config's timeout to 2 seconds: <httpRuntime executionTimeout="2" />

I expected it to crash with timeout error... but it did not happen.:-OFew experiments showed that executionTimeout works only if all of the following is true:1) Domain name is not localhost (to test timeout you should use "YourComputerName" instead of "localhost").2) Project is compiled in Release mode.3) <compilation debug="false">If any of the above is not true then executionTimeout length is virtually unlimited.On top of that IIS typically times out later than executionTimeout limit asks it too.When I set executionTimeout=2 and made my page request to sleep for 10 seconds, I was getting "Request timed out." response only in ~40% of requests.

Monday, September 13, 2010

I spent almost couple of days trying to figure out what caused WCF service to crash (in a weird way) when it was tried to return large resultset.Initially the problem expressed itself on the WCF client side. When number of records in returning results was close to 5000 – WCF client generated Meaningless "An existing connection was forcibly closed by the remote host." exception.Google search for '"An existing connection was forcibly closed by the remote host." WCF size' brought upWCF issues sending large data forum discussion.The right answer (maxItemsInObjectGraph) was mentioned there, but it was buried under pile of misleading suggestions.

One step toward the solution was to use soapUI utility to make the requests (instead of calling WCF service from another .NET client).That helped to determine that the problem is on the WCF server side -- soapUI simply couldn't get any response (when number of returning dataset rows was ~5000+).

What really helped to find the final answer -– was enabling WCF diagnostic by adding this to web.config on server side:

Then app_tracelog.svclog revealed much more specific error message:---Maximum number of items that can be serialized or deserialized in an object graph is '65536'. Change the object graph or increase the MaxItemsInObjectGraph quota. '---Quick googling for "maxItemsInObjectGraph" brought me to MaxItemsInObjectGraph and keeping references when serializing in WCF blog post which recommended to add the following section to WCF server web.config: