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