DbViewSharp and Sql Server permissions

I somewhat neglected the subject of database permissions when developing the SQL for use in DbViewSharp. This is because I have sysadmin access to development databases, which lets you do just about everything. However I'm pretty sure there will be times when you need to connect to databases that your DBA will not grant you sysadmin access to. This post will describe what happens in DbViewSharp when you connect using an account with fewer priviliges than sysadmin.

Sql Server defines some preset database roles, which setup common patterns of access to the database objects. There is a read-only role, a write-only role, a master role etc. Attaching a login to one of these roles removes the necessity to confgure a set of permissions for each new login. Instead they inherit the permissions of whatever roles they are assigned to. Subsequent paragraphs will examine what happens when you connect to DbViewSharp with the permissions of some of these roles.


Public.
The public role is granted the fewest permissions out of the box. In fact it is granted none at all save to be able to connect to a database. Once connected though you will not be able add, modify or delete rows from any table or even to view the data in any table. It is pretty pointless to associate a login with the public database role unless the DBA has manually added either select, add, modify or delete permissions to at least one table or view in the database.

When you only belong to the public role you are likely to get a limited view of the objects in the database, or if not limited, then almost certainly an incomplete one. Use menu option Show, Permissions to show what operations you can perform on each table you have access to.

There are further restrictions to this role. You may not examine the source of any view, stored procedure, function or trigger even if you have the relevant permission to use it. Needless to say all the items on the Sql Server menu are either inaccessible or produce very limited reports. If at all possible try to find another account to login with.


db_datareader
This is the basic built-in read-only role. Things start to improve slightly with this role. At least you get to see all of the objects in the database (tables, views, stored procedures, functions and triggers). You can also view the data in tables and views. But you still cannot see the source Sql of the views and stored procedures. Nor are the processes, locks, jobs and replication reports available. Disappointingly for me you do not get to see the row count next to the table name either. I happen to love this feature, finding it unexpectedly useful for orientating myself in the table list.

Belonging to the db_reader role is better than nothing, but still limited.


db_owner
Now we're talking! You can see all the tables, the table row counts and all view and stored procedure source sql. The processes, locks, jobs and replication reports still do not work so it looks like you need to be assigned the server role (not database role) sysadmin to obtain access to that data. For day-to-day work though the db_owner role is good enough to login with.

The problem with the db_owner role is that with the power to view all the goodies that DbViewSharp can give you access to comes the additional power to create and destroy almost anything in the database. In any environment where you do not have access to administrator priviliges you are unlikely to persuade a DBA to attach your login to the db_owner role. If you can then lucky you! What the rest of us need to do is to beg the DBA to give us read-only access to the objects plus a tweak or two to get access to the view and stored procedure source.

We've already seen that you can get read-only access to everything through the db_reader role. To this we need to add two more permissions to our account. From SQL 2005 on there is a specific permission available called "View Definition" which can be granted per-schema or per-object. Our login will need this permission. To get the table row count you need permission to view Database State. Why? Because the row count is not obtained by executing a sequence of "select * from sometable" queries; that would be way too slow. Instead it queries a dynamic management view, which is a sql server system object.

Below is a script that you can ask your DBA to run that will give you DbViewSharp access to a database named "Warehouse". Change login name, password and database name to suit.

use master
CREATE LOGIN [DbViewSharp] WITH PASSWORD=N'dbviewsharp', 

                                CHECK_POLICY=OFF
GO

use Warehouse
GO
exec sp_grantdbaccess 'DbViewSharp'
go
EXEC sp_addrolemember 'db_datareader', 'DbViewSharp'
go
grant VIEW DEFINITION on schema::dbo to [DbViewSharp]
grant VIEW DATABASE STATE to [DbViewSharp]
go

The Mysterious Behaviour of the Filter Box in DbViewSharp

How often are you baffled by the way a particular feature in a program works? Sometimes it will do this, but it is equally likely to do something else in what seems to be the same situation. Of course the situations are not the same in both cases from the point of view of the program, or more accurately the programmer who designed and developed the feature, but that does not help you if you do not have an in-depth knowledge of the workings of the program. As a consumer of software written by others I should know better. I'm guessing that DbViewSharp is packed full of such features; blindingly obvious and really clever to me as designer, developer and chief user; infuriatingly fickle to anyone else just trying to to get a job done. This post will take a detailed look at one of these features, the Filter Box, and describe it in depth so that you can come to regard it the same way that I do, as an incredibly versatile timesaver.

First of all what is the Filter Box? Well its the drop down combo box that sits above the main grid.

You first notice it when you connect, because it flashes red until the table list is displayed. This is just an overload of the control to act as visual feedback when a slow query is running. Once the grid is displayed then entering text into the box and hitting Enter or pressing "Go" will change the grid display by using what you just entered as a row filter.

When the table names are displayed the filter will match on the table names. However I am trying to apply more of a browser style search than a strict SQL query to the matching. By this I mean that instead of having to enter exactly what you want to search on, taking care of case and punctuation (punctuation in this sense refers to underscores in table names), you just need to enter a substring that your target table name or names contain and which will limit the grid to rows with just those tables you are interested in. Like Google you may see items in the grid that you are not interested in, but just like Google with a tiny bit of practise and basic knowledge of your schema you soon discover the minimum effort required to bring your tables into view from even a large list. Google does not require you to enter more than a few keywords. DbViewSharp does not (normally) require more than a few characters. Google matches the keywords anywhere in the page. DbViewSharp matches the characters anywhere in the table name. Unlike Google however DbViewSharp only searches correctly for a single substring. If you try entering two separate substrings nothing will match as the two are treated as a single substring with a space in it.

Things get more complicated when you are displaying data from a table. Here is where the fun starts. It's fun because you don't appear to get a chance to explain to DbViewSharp what columns you want the filter to act on, yet it goes ahead and filters the rows anyway. Sometimes, bafflingly, it doesn't filter any rows. So lets try to change this from confusing to genius.

Unlike the tables list there could be many columns of table data in the grid so which of them are used in the filter search? It will help you to understand what is going on if you right-click in the top left corner of the grid following a filter search. This causes a context menu to appear from which you can select "Show SQL". This in turm does just as you'd expect. It reveals exactly how the filter text is put to use to search the grid data. Here's an example from one of my test databases.

select top 500 [id],[name],[class],[typeof],[fb_id],[description],[STATUS] 
 from Animals
where ([name] like '%bat%' or [class] like '%bat%') 

Note that two columns were used in the search apparently at random. However it is not random. The parameters for the grid display were generated automatically when the table was double-clicked. The routine that decides on the filter columns examines the first three columns in the table and assigns them to either a text filter list or a number filter list depending on their type. The reasoning is that normally the most important and searchable columns are added to the table first. Look at my example. The first three fields seem pretty important, particularly id and name. The id is very likely to be a numeric key field and so is excluded from an alphabetic search.
When I entered "bat" in the text box I was interested to find rows of bats, which I get and without writing a single line of SQL. I'll repeat that. I found the bats in the table without: 1) remembering the SQL syntax; 2) typing in any keywords; or 3) writing a fiddly where clause. I simply typed "bat" into the filter box and pressing "Go". It's the same when filtering on numeric data. If you enter a number in the filter box it assumes you are looking for a key or keys with the value entered. So if you know the id of a record just enter the number in the filter box and hit Enter. If the id is good you should see your record.

select top 500 [id],[name],[class],[typeof],[fb_id],[description],[STATUS]
 from Animals
where ([id] = 7002)

Another common search requirement using keys is to search a line-item table for all rows related to a master record (think Customer orders or Invoice line items). As long as the master record Id is in the first three fields of the line-item table you will get this type of look-up for free as well.
The following situation has been a common scenario in my professional life. Given the ID of a record that has a problem find the cause and fix it. Without DbViewSharp this is the start of an awful lot of ad-hoc SQL writing. With DbViewSharp I start by checking the problem record in DbViewSharp like so:

1. Connect to the database to obtain the table display.
2. Locate the table name using the filter box.
3. Double-click to view the data.
4. Enter the record id in the filter box.
5. Double click on the record line to view the data in profile (ie. one row per column).
6. Confirm that the error is in the data, then scan for other clues and continue to hunt through data and stored procedure code using DbViewSharp until the job is done.

The big gain, I think, is that once you are familiar with how to use DbViewSharp and the way the filter box works then you do not not need to context-switch from thinking about your trail to the cause of the problem and how to write the SQL you need to get the information you need to continue on the trail.

What if the automatic filter column selection lets you down? Suppose the master record Id is not selected as a number filter. You can modify the filters using the Data, Change view option or pressing the toolbar button that looks like a window, located to the left of the funnel icon. Select fields from the DataView Fields list and use the relevant [>] and [<] buttons or drag and drop to add or remove them from the Text filter and Number search lists.

Changes you make to the automatic DataView will remain in place until you quit DbViewSharp. If you find yourself repeatedly changing the filter fields consider saving them as a new DataView.

Now you have a clearer picture of how the filter box works on searching the data I need to describe how to manage the filter box itself to achieve maximum fluency.

• If the focus is on the grid then when you type anything the focus switches to the filter box and the text is displayed there for searching. The grid gains focus after it is displayed.
• Use the escape key to clear the filter box and redisplay the grid unfiltered. 
• Previous search text is added to the drop down. The list is maintained in last-used order. This is massively useful when tracing records with the same Id over multiple database. 

There is subtle logic used to determine whether to use the filter text or clear it. If you have just applied the filter search to one set of objects and are switching to another then you probably do not want the filter to be applied to the new object list (eg. switching from filtered tables to views). If you have just entered a new filter and are switching then you probably do want to apply the filter otherwise why did you bother to enter anything?. However there is a common scenario where this logic is wrong. This is the case where you enter filter text and search only to realise that you just searched the wrong set of objects. For example in your mind you are searching the list of views, but the grid was actually displaying tables. When you switch to views your search disappears and the full list appears. You can recover the search text from the list connected to the filter box, but I found this happening so often I programmed a short-cut for it. The clear search toolbar icon (looks like a thin white rectangle) behaviour is now to clear the text box and restore the full list if it contains text, but, importantly, to restore the previous search text and apply it to the current grid if it does not contain text. That is the last piece of the jigsaw that enables this smart filtering to work so well.

Final tips
Occasionally you may be frustrated that you cannot search for numeric data in text columns because numbers entered are matched to numeric columns. The workaround for this is to explicitly include % in the search text, but then it uses proper SQL rules; so %10% (anywhere), 10% (starts with) or %10 (ends with) will match correspondingly: "Sport-100 Helmet", "10-speed aluminum derailleur" or "Hex Nut 10".
You can include a % in non-numeric searchs and the same rules apply. The most common case is to search for items starting with the text. This may be useful if your objects follow a naming convention that uses prefixes or schemas. In the MS AdventureWorks sample database for example, you can filter just the HumanResources tables with "H%".

Conclusion
The filter box originated from my profound irritation with the way that SQL Server Management Studio requires you to type without pauses and without mistakes to navigate the table lists. It was then extended and refined over years of (personal) use to become the means by which I can satisfy at least 90% of my data-searching requirements in DbViewSharp. I hope this post has given you the encouragement to experiment and get to understand it a little better. Don't forget to look at the SQL.



DbViewSharp is an open source desktop application that connects to SQL Server databases and allows you to search for just about anything important. Find DbViewSharp on Codeplex.

DbViewSharp in the Field

I may have mentioned this before, but I use DbViewSharp every day. That's why it has become a Swiss army knife of an application. In an attempt to promote it amongst my colleagues (all die hard Sql Management Studio users alas) I tend to fire it up at every opportunity to answer any database-related questions they may ask me. This sometimes misfires, but occasionally scores a hit. Today was an example of the latter.

A colleague was asking my opinion on the typical number of sub-records would be attached to a main record in a certain schema. Rather than make an educated guess I switched to DbViewSharp (already running of course), connected to the relevant database

sought the relevant sub-record table

and opened the data view. The sub-record includes a sequence number field so I selected a cell in that column and pressed the aggregation toolbar button.

The effect of this is to produce a sorted count of all the instances of the sequence number.

From this we could see that every main record contained a sub-record with sequence #1, but only 5% or so contained sequence number #2 with subsequent numbers dropping off to a maximum of around 100 sub-records associated to a main record. Below is the core of the query used to produce the grid:

select SEQ,
       count(SEQ) as [COUNT]
from SUB_RECORDS
group by SEQ
order by count(SEQ) desc, SEQ

DbViewSharp adds a few exotic touches such as a row limiter, some dodgy code to enable the query to count null records and a search clause to enable you to apply a filter. Since this aggregation function can be applied to most types (don't try it on Xml and binary types) it's useful for obtaining an overview of how any field with a limited range of values is populated; eg. proportion of 1 or 0 entries in a flag field, most common state in a US customer table, customers with the largest number of orders or sales and so on. In many cases using this feature avoids having to write a custom query. A further nice touch is that if you happen to discover an unexpected value in the report (a number out of range or an unusual text value)

then just double click on the row with the odd value to display all of the row data for any record that contains that value.


This can sometimes be the start of a trail to a problem in your data.

DbViewSharp is an open source desktop application that connects to SQL Server databases and allows you to search for just about anything important. Find it on Codeplex.

What is DbViewSharp and why would you use it?

DbViewSharp is an open source desktop application for searching SQL Server databases. But then so is SQL Management Studio. So why do I use DbViewSharp instead of SQL Management Studio (SMS)?

When I want to find out something about a database; the fields in a table, the data in a table, the source of a view or stored procedure, I want it fast; there's usually an urgent problem. I don't like waiting for my program to load then finding the connection or even re-entering the connection details; then expanding the list of database names (why so slow?); then finding the database name in the list of databases; then expanding the list of table names; then finding the table name in the list of tables; then right-clicking and finding the item in the context menu to show the data; then choosing whether to select 1000 rows to view or 200 rows to edit. Then waiting while the application fetches 4, just 4, rows of data. I've just done this on SQL Management Studio and it was painful. While I'm on a slow machine at the moment I'm going to try the same thing on DbViewSharp.

Ok then, launch DbViewSharp, it's faster to launch, but not so you'd notice. Click to connect to the database and right there are the tables. Find the table, double-click on it and right there is the data; pretty much instantly for a table with 4 rows in it.

You want to see the source for a view? With SMS you expand the list of views, you find the view you want, right-click to get the context menu, search down the list of options, figure out you want "Script view as...", then Create to..., then New query window, wait for a moment, and finally see your view source. I wont bother describing what you have to do to compare the source of the same view in another database. In DbViewSharp, click on the views toolbar button and you see your views. Double-click on the view and you see the source. That's all.

SMS is a mighty application, which does (almost) everything that DbViewSharp does and much, much more. However it was designed so you could manage every single aspect of a SQL Server database through a GUI and compromises its search functionality with its CRUD features and all-inclusivity. DbViewSharp is tuned for searching, which is what I've wanted most for the last 8 years of working with databases.

I hope the examples I've run through have given a idea of the different design approach I took with DbViewSharp. There are so many more cases where I've taken the liberty of making a common search task easy to perform possibly at the expense of omitting some functionality I need once in a season.

I hope to expand on this in future posts, but for now if you want a change from  SQL Management Studio  and are tired of looking at endless clones based around a tree of database objects and a query window then give DbViewSharp a try. Find DbViewSharp on Codeplex.

First Post

Hello readers.
I'm David Moore, developer of DbViewSharp, an open source program that aims to provide an agile tool for performing a wide variety of tasks with a Sql Server database. My documentation skills are undisciplined so I've decided a blog is the best way to publicise tips on use, news of releases and features and perhaps small articles on the Sql behind some of the functions.