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
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
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…