Sql writer not able to find the new table

Question

Sql writer not able to find the new table

sql_novice_2007

Hall of Fame

Points: 3307
More actions
January 17, 2012 at 12:30 pm

#253620

I have a CMS database that is created as part of an installation. I created a new table with the same dbo schema but when i try to access the tables to run a report with sql writer I can see all the tables except the new table I created.
What could be the reason?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if the user explicitly says SELECT * FROM NEWTABLE, does he get an error stating "object does notexist or does not have access"

if that is true, it sounds like the user has been assigned rights on a per object basis, and not at the schema levle.

so for example, if i add a new user, and GRANT MyUser SELECT ON dbo.invoices, that user can see touch one and only one table;

if i created a loop that went thru all the objects currently int eh database, he'd seem to have access to everything, but when new objects get added, he'd need accesss to teh new objects added.

you can grant access via a role, like db_datareader, or even at the schema level instead, I believe, so all objects created under the schema (dbo in this example, right?), the user would inherit the new objects automatically.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 2

sql_novice_2007

Hall of Fame

Points: 3307

January 17, 2012 at 12:41 pm

#1434805

The user can execute DMLs against this table.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Also, confirm which schema this new table was created in. You may have permissions on the schema but the object may not exist in that schema.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 4

The new table is also created in the dbo schema that other tables also belong to.

Lowell SSC Guru Points: 323518 More actions · Answer 5

well my first guess was off i guess, but i still think it might be permissions.

lets go back to the basics, i guess: you said this:

when i try to access the tables to run a report with sql writer I can see all the tables except the new table I created

we are talking Visual Studio and reporting services and the Server Explorer, right? could it be a refresh issue? what credentials are bing use din visual studio (could they be different than when you connect via SSMS?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!