December 7, 2012 at 6:49 am
Hi all,
I recently backed up a SQL 2000 (SP4) database and restored it on to a new server 64-bit Windows Server 2008 R2 running SQL 2008 (SP3), shutting the old database down. The exercise seemed to go without error and all application/various system processes that access this database continued to work fine once it was made available again in the new location (hurrah for CNAMES!).
The problem I'm having is that for a couple of users, whenever they use SSMS (2008 or 2008 R2 client) to try and expand the tables of some of the databases, they get the following error message:
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Unfortunately clicking on help for this message doesn't yield the most helpful response! I know the mssqlsystemresource database is not directly accessible in terms of assigning permissions etc... and I've looked for any kind of 'deny' (e.g. db_denydatareader) permissions that might be causing this both directly and indirectly (i.e. Windows Groups that are also logins on the SQL Server which contain the login) for the user concerned, but I can't find any. Doesn't seem to be anything logged about this in the SQL error log.
Although the new server is mixed mode authentication (as was the old) for a couple of necessary SQL logins, all users I'm talking about here are using Windows Authentication to connect.
Some other (Windows) users seem fine and when I completely delete the login and database user(s) for one of the people experiencing the problem and recreate them again, it doesn't resolve the problem. When I created a new test user, it worked fine.
After spending a few hours searching through various forums, I've tried running things such as:
* Granting the user as db_owner of the database (far from desirable, but I could cope with that)
* Granting SELECT permission to public user sys.configurations object
* Granting VIEW ANY DEFINITION TO (on master)
* Checked for things like orphaned users etc.. There weren't any.
* The mapping between the logins and users seem to be OK.
Unfortunately none of these things have worked.
It seems the only thing that will allow them the access they need is to grant them the server role of sysadmin. I REALLY don't want to have to do that, but as at now it looks like I might be forced to. I'm not in a position where I can start rebuilding the new server etc...
Can anyone think of any ideas as to how I can fix this without granting the sysadmin role?
Regards and apologies in advance if this is an easy fix. I did do a fair amount of researching before posting, but I may have missed something obvious.
Chris Stride
December 7, 2012 at 10:40 am
As painful as it might seem, have you considered scripting ALL the database objects, then backing up the database, then detaching it, moving the MDF and LDF files somewhere safe, then re-attaching it under a different name, then recreating the database and using the generated scripts to recreate all the objects, followed by copying all the data back in ? Perhaps something done over a weekend ? Not pretty, I know, but then I could be missing something just as you may be.
I guess maybe you should consider turning on some error logging and see what ends up in the error log when these errors occur and see if that gets anywhere before you go down this road.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 3:10 am
I would start by identifying the statements these users run and taking it from there.
Old SQL code may be relying on statements that can only run by sysadmins (results of some extended procedures etc)
There are usually alternative ways to get the same results without needing excessive permissions.
I know of some (badly written) 'black box' commercial apps that rely on such code and only run if the users are sysadmins. One workaround is to ensure that these logins cannot be used by tools like SSMS.
Cheers,
JohnA
MCM: SQL2008
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply