Geckoware’s SQL Scribe, and “schema access”

It’s been a very busy week this week, so I’ve only got time for a quickie (stop sniggering at the back).

We have recently gone through a big server migration / consolidation exercise.  As is normal in these situations, some users have gone from being sa and lords of all they survey to being “normal”-ish users.  Unfortunately, this sort of thing isn’t always well-received, particularly when something they’ve come to rely upon breaks.

In this instance, the user in question used a piece of software called “SQL Scribe” from Geckoware, to generate a schema and dependency map before changing something in one of their databases.  The account used to run this software was granted sysadmin rights under the old environment, but not under the new.  So I, as a conscientious DBA tried to find out what rights this piece of software actually required.  The body of the response from Geckoware is reproduced below:

Scribe needs schema access.

Helpful. Just what does that mean?  And how do we implement this?

Given the description of the software, it would appear that it needs to be able to either read the contents of the “INFORMATION_SCHEMA” schema, or to run queries directly against system tables such as sysobjects etc.  (Yes, sysobjects – this is a piece of software written for SQL Server 2000 & 2005, and hasn’t (seemingly) been updated for three years – at least, if its web page is anything to go by.)

So let’s try granting the appropriate minimum permissions:

use <<dbname>>
go
grant view definition to <<username>>

(Note – if you want to grant access to all schemas in one fell swoop, grant “VIEW ANY DEFINITION”…)

Anyway, the software still fails. So this time, we’ll do it properly. On the old system, of course, run the software with full sa rights while running a SQL Profiler trace. And what do we find? A call to

sp_databases

which requires us to be running as a user with “public” access to the master database. So we grant that, and have another go. Ignoring the numerous occurrences of deleting temporary tables without checking to see if they exist in the first place, and a few other oddities, I finally found it. A call to

dbcc showfilestats

Excellent. Undocumented and unsupported by Microsoft… Permissions required for DBCC? Almost certainly db_owner, or sysadmin. It works with db_owner…

Right. The batphone is ringing (again). I bet it’s the same issue as it was an hour ago…

Advertisements
This entry was posted in SQLServerPedia Syndication, Uncategorized. Bookmark the permalink.

2 Responses to Geckoware’s SQL Scribe, and “schema access”

  1. thomasrushton says:

    In the spirit of full disclosure, I did get the following response when I pressed for more information:

    This tool is really in the range of system tools. It therefore needs almost all permissions (no data access , just schema). I normally tell people to schedule it to run as a batch job and then distribute the output to the users that require it. In this way you can create a user or run it under a sysadmin.

  2. Pingback: Documentation with Red Gate’s SQL Doc | The Lone DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s