[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SELECT Permissions denied on MSSQL tables

Posted on 2009-02-20
12
Medium Priority
?
1,732 Views
Last Modified: 2012-05-06
Hi there.
 
I was recently granted access to a database on server BF-SQL1. However, I cannot view any of the tables due to an error creating the message:

SELECT permission denied on object 'extended_properties', database 'mssqlsystemresource', schema 'sys'.


I have searched everywhere and have not been ale to find anything to help me.

please help!!!
Thanks...
0
Comment
Question by:suicehockey44
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 23692809
what are you doing when you get this error?  

you could have someone give you this permission

grant select on schema::sys to yourusername

or you could just have them make you a db_datareader in the db.
0
 

Author Comment

by:suicehockey44
ID: 23692862
Thanks champmandew...I will contact the sysadmin and let him know. Be right back!!
0
 

Author Comment

by:suicehockey44
ID: 23692912
This error occurs as soon as I click on the "Tables" folder in the SSMS Object Explorer.
Still waiting from sys_admin...thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:chapmandew
ID: 23693006
sounds like the sysadmin just needs to give you permission to log into the database for which you're trying to look at the tables.
0
 

Author Comment

by:suicehockey44
ID: 23693225
Still no luck. I am the db_owner.  Why can't I see these tables??? I can create the tables, because they show up on his login. Hmm....

0
 

Author Comment

by:suicehockey44
ID: 23693269
When you say Log into the database, what exactly do you mean? Im a newb big time...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23693290
You have been explicitly DENYied permission somewhere.  DENY overrides other permissions (except for 'sa', of course, who can't be DENYied access to any object :-) ).

It's most likely from a group or role you're in.  
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 23693292
tell the admin guy that you need to be able to log onto the sql server instance, and that you need to access the databases on there.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23693406
I don't think you can query the system db:
mssqlsystemresource
directly anyway.

I don't think SQL allows anyone to query that db, even sa's.
0
 

Author Comment

by:suicehockey44
ID: 23693447
I see.  The error message implies that the I am trying to access the mssqlsystemresource. However, I just want to view the tables in my SSMS.  

I am connected to the sql server instance.

I can create table which the admin can see, manipulate, etc.

I can even create my own schema.

Ugh...
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 23694145
The mssqlsystemresource database is a resource database that is hidden and is used to manage metadata for SQL Server.

What the error message is telling you is that there is information in the extended properties of the table that you do not have access to.  I am not sure why you cannot get to them using the dbo user.

But one thing is for sure, if the admin can see these and not get the errors, then it is a permissions issue and you need to have the sysadmin determine the permissions that are being explicitly denied so that it can be corrected.  DENY does override the GRANTs, so there is something like View Definition permission or some other permissions that have been denied and is causing you problems.
0
 

Accepted Solution

by:
suicehockey44 earned 0 total points
ID: 23819463
I have found another method of doing this. Thank you.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question