problem with db_datareader role

[janosch99]

I use a database on a remote server through webservices with ado.net functions.
When I login as a user that has db_admin rights, everything works fine.
now i asked the administrator of the server to create u new user for me, with only SELECT permission. He created a user with the db_datareader role.

When I try to query the database with this new user through ADO.NET, I get the same message on every SELECT statement: invalid object name '[tablename]', for any table in the database.

I don't understand why this doesn't work, and especially I don't understand the error message. When there are insufficient right, I would expect another errormessage. Why a message that tells me that a table that DOES exist doesn't exist? The server administrator cannot help me any further.
When I try the same routine on a local MSql database, it all works fine. The difference is that I grant all the permissions through ADO with transact-sql and the server administrator used the Microsoft Enterprise Manager
Does someone know any explanation?

 

[JohnDTampaBay]

If you don't specify the object owner name, SQL Server assumes the user is the owner. So if you db_datareader user is bobo, the statement

SELECT * FROM Table1

would be interpreted as

SELECT * from bobo.Table1

which probably doesn't exist. Specify dbo as the owner in the SELECT and see if you still get the error.

SELECT * from dbo.Table1

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[janosch99]

thank you

this is funny, I just found the answer myself.

but... now I have another problem!
My server administrator has given the database owner the name: user-woordtraining.

the problem now is that I get an error message about this "-" in the name, even if I use dbo instead.

looks like we have to change the name of the database owner? is that right? or do we have other solutions?

 

[JohnDTampaBay]

Hmm. I know in the SELECT you can bracket the owner name.

SELECT * FROM [user-woordtraining].Table1

But not sure about the user name. Why prefix a user name with "user-". That would be like prefixing every table name with "table-". I would avoid all special characters in object names except maybe underscore as most have other usages (hence the term special characters ).

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[janosch99]

thank you

unfortunatly, even bracketing doesn't withold the server sql compiler from prompting me about the improper use of the '-'.

Is this obligation to refer to the object owner new in the latest version of SQL server?. I have Microsoft SQL Server Desktop Engine (downloaded last year) and that doesn't bother me with this.

 

[JohnDTampaBay]

My answer was not complete. From BOL, owner names > Object Visibility and Qualification Rules:

Similarly, when you refer to an object, Microsoft® SQL Server™ uses the following defaults for the parts of the name not specified:

Server defaults to the local server.
Database defaults to the current database.
Owner_name defaults to the username in the specified database associated with the login ID of the current connection. If that user owns no object with the specified name, SQL Server looks for an object with the specified name owned by the database owner (dbo) user.

Is it complaining about the login or the object reference? Is the login's default database correct?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Catadmin]

You might want to log in as the Sysadmin account and take ownership of all objects owned by the user in question. This way, the owner will change to DBO and you won't have to worry about it.

If a user has Create Table, Create View, or other Create Object rights, unless they specify their "Create" statement with the owner.object (like Create Table DBO.Accounting), SQL Server will automatically assign the owner as the currently logged in user (like Create Table woord-training.Accounting). The only time this doesn't happen is when someone is logged in as Sysadmin/DBOwner. Then SQL Server automatically calls DBO the owner of the object in question.

Something to keep in mind when assigning roles and permissions to non-admin users.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[janosch99]

ERRATUM
I don't what I did this afternoon, but I just noticed that the solution with the brackets DOES work. So my problem is solved. Thanks to your help and my sloppyness I learned a lot about microsoft Sql server administration today.