March 21, 2006 at 12:23 pm
Hi All,
We recently shifted to SQL Server 2000 to SQL SERVER 2005. And I have a call in one of my stored proc as below
EXEC sp_Msforeachdb "SELECT name, '?..' + name FROM ?..sysobjects WHERE type ='P' "
Which was working very well in SQL SERVER 2000 but getting
error message
SELECT permission denied on object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.
error in SQL SERVER 2005.
Cannot I refer objects (here stored proc) from all my user defined databases? Do I have to do differently? I donot wnat to give any additional roles or permissions to my application user who is executing this stored proc, except the execute permission.
Thanks,
March 22, 2006 at 2:58 am
It's because SQL 2005 is more secure by default than 2000, so you won't be giving them any more access than they had in 2000.
But to remain secure, as that table lives in the sys schema, you can use another account that has access by sticking EXECUTE AS at the front of that command.
How's that?
March 22, 2006 at 3:11 am
Just a note/warning. All that wonderful code out there that uses undocumented stored procedures (like sp_msforeachdb) and makes use of system tables (like sysobjects) are likely to have problems when migrating to SQL 2005, iether due to the increased levels of security as Joseph pointed out, or due to the changes in the way system tables are managed. All access should be via the sys schema.
I'm a bit scared of all the custom code I'm going to have to review, like my log shipping solution, but fortunately I expect to move to database mirrors and snapshots (in SP1) when a migration hap[pens in my world.
CiaO 4 NoW
May 7, 2007 at 10:35 am
I had the same problem and I had references to sysobjects and syscolumns that worked on SQL2K. For 2005, I changed "sysobjects" to "sys.all_objects" and "syscolumns" to "sys.all_columns" and it all worked.
May 26, 2008 at 1:58 pm
Hi,
I am receiving the same error trying to access the "User Profiles and Properties" of a Sharepoint 2007 SSP on my SQL Server 2005 (as Event 7888).
Since I obviously cannot change anything within the MOSS implementation's stored procedures I feel a bit lost here.
My resolution seems to be that the curently used account needs some more (well, at least the SELECT right) on the mssqlsystemresource database.
However, after having looked through the search engines I could not find anything useful so far.
Anyone had the same error and willing to give me a hint?
Thanks in advance,
cc
May 26, 2008 at 3:12 pm
have u tryed master.sys.objects?
..>>..
MobashA
May 26, 2008 at 3:52 pm
MsSqlSystemResource is a hidden system database that is only accessible through the pre-defined objects in the "sys" schema, supplied by Microsoft. It is not intended to be accessed directly by anyone except Microsoft.
If you want learn more about it or would like a trick to be able to directly access a copy of it, try this article: http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 3, 2008 at 8:44 am
It does not seem to make a difference what I do -- I create a fresh database through the windows authenticated super user,then add a login, then add a database-specific user with all the permissions boxes checked.
Then when I try to login with said user (that should have all permissions for this one database) I get this same error message whenever I try to see the tables in the fresh database.
How do I make a user so that this error message does not appear?
October 4, 2008 at 3:17 pm
I believe that this is either a SQL Server bug or a corruption of one of the system databases. I would recommend contacting Microsoft.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 1, 2009 at 3:31 pm
question:
if your user has public and sysadmin, can you still deny 'Select'
January 1, 2009 at 3:36 pm
i think u another sysadmin user can..
..>>..
MobashA
January 1, 2009 at 4:30 pm
if you perform in 2005
deny select from sysobject to [specificuser who has sysadmin role]
it won't let you
January 2, 2009 at 5:29 am
I created one test login and granted no other permission except default one. After that I ran your code and it ran perfectly fine.
Microsoft SQL Server 2005 - 9.00.3042.00
MJ
February 6, 2009 at 3:16 am
I had the same problem, because the user had the db_denydatareader property checked.
I unchecked the property and it worked well.
March 24, 2009 at 2:20 am
i am also having the same problem, i have given all permissions but still i get this error. actually i am trying to install dotnetnuke when i connect to database this bad error occurs, i created fresh database granted full permissions but still in hard luck.... do not know what to do now???
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply