Link to home
Start Free TrialLog in
Avatar of panspermia
panspermia

asked on

960 POINTS! I just need to Query a SQL DB with a ReadOnly account

I have two questions on this.  One right answer gets both sets of points.   What I'm asking seems so easy, I just want to look up a value in a SQL database with a read-only account!  Yet for some reason, nothing is working.   My SQL DB has 2 accounts- a full access 'admin' account and a read only account that has read access to all my tables.  
In VB.NET i have this basic function to query a name that a user puts in a textbox to a SQL table.


Function Query()
        SqlConnection1.Open()
        Dim Finder  as string = ("SELECT * FROM MyTable WHERE user_name= 'MyName'")
        SqlDataAdapter1.SelectCommand.CommandText = Finder
        Query = SqlDataAdapter1.Fill(DataSet11)
        SqlConnection1.Close()
 End Function

My SQL connection string:
The code works perfectly when I use my Admin account

       Me.SqlConnection1.ConnectionString = "user id=<ID>;password=<PASSWORD>;data source=""<IP>"";initial catalog=MyDB"


HEre's what confuses me:  The entire application works perfectly when I put my admin account info into the SQL connection string.  If i put my readonly account info in there, it invariably gives me an error (shown below)

"Is your read only account info setup right?"  Yes!  I have verified this many times.  In fact, in VB.NET I create a new sqlconnection and I can successfully test the connection and even fill in my dataset using the readonly credentials.  SO i know the read only credentials are totally working.  Its getting the code for VB.NET to query a table with readonly credentials that has me stumped.



ERROR:

"invalid object MyTable"  (this is what a Sql catch exception returns)


I simply change the SQL string to my admin account and it works great.   I know that my read only account works because in the form designer not only can I successfully create a SQL dataadapter, but i can also preview the data and the dataset!   Its when I use the command 'Query = SqlDataAdapter1.Fill(DataSet11)' in my code that I get an error.  So why in the world can I preview the data and fill the dataset in the form designer with the readonly account, but when i run the code, it bugs out?

Avatar of toddhd
toddhd

Just for kicks, try fully qualifying your object names, that is, the table and the field. For instance:
SELECT * FROM dbo.MyTable WHERE dbo.MyTable.user_name= 'MyName'
try this:

'does the dataset has the table?

Query = SqlDataAdapter1.Fill(DataSet11.Tables("MyTable"))




Function Query()
        SqlConnection1.Open()
        Dim Finder  as string = ("SELECT * FROM MyTable WHERE user_name= 'MyName'")
        SqlDataAdapter1.SelectCommand.CommandText = Finder
        Query = SqlDataAdapter1.Fill(DataSet11.Tables("MyTable"))
        SqlConnection1.Close()
 End Function
The thing is, I just don't see how this is a problem with the code, for several reasons. First, the error message is coming from SQL Server, not .NET. It is complaining that something doesn't exist, but that's likely because it does not have access to it.

Second, if the code works with the  admin password, and all that changed is the login, then the code is fine. This again is indicative of a permissions issue in SQL Server.

I'm assuming that when you said you created a SQLConnectionObject in the designer and it worked, that you were doing so through the IDE. Do so again, and then explorer the generated code to see exactly what the connection string is it is using. It may be using some sort of integrated security in addition to your credentials. It may also be using the ASPNET account or System account to login.

Have you tried this simple test? Just open up the Query Analyzer, login as readonly, and run the query by hand. That will be your BEST determination if this is working correctly.
ASKER CERTIFIED SOLUTION
Avatar of gajender_99
gajender_99

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hmmm...

You say "basic function to query a name that a user puts in a textbox to a SQL table"

I assume MyName is a variable that contains the value from the textbox? If this is the case, then the SQL Command is wrong if you're doing it via code... The code should be...

Dim MyName As String = TextBox1.Text
Dim Finder As String = "SELECT * FROM MyTable WHERE user_name = '" & MyName & "'

Try also adding "Trusted Connection=False" to your connection string...


By The way... whats the exact wording of the error you get???
did you check if the users have permission to access that table?

- Have you tried connecting through the SQL Analyzer with the readonly user and execute the SELECT statement?

or

- Try adding the readonly user to administrators role. just to see if it works.

Check to see what the initial catalog is of your read only user in Enterprise Manager. You're specifying initial catalog in the connection string which, you would assume, would override the sql user's initial catalog setting.

It's something I've never tested (and I'm too lazy to switch to my dev box right now), but it doesn't hurt to check.

While you're at it just double check that your read only user absolutely has access to your database and SELECT permissions on the table you're working with. I agree that it has to be a SQL server permission/access issue.

Paul
For kicks you might actually run the sql statement in query analyzer and see if it returns any kind of a funky error when granting select permissions.

USE YourDatabase
GRANT SELECT ON YourTable TO YourReadOnlyUser

Paul

Avatar of panspermia

ASKER

"
("SELECT * FROM myschema.MyTable WHERE user_name= 'MyName'")"


BRILLIANT!  BRAVO!!

for some reason this is required for a readonly SQL account on a table, but not with a full access account on a table

u rock man.
i just noticed that toddhd  also had the right answer.  i will give toddhd the 455 points from my other question.  so in effect, i'm splitting the points.
toddhd please leave ur comment on this question:  "Simply query to a SQL DB with a Read Only account"  so i can give you the points for it.  

thanks so much guys.
Thanks panspermia, but for the life of me, I can't find that question. Was it closed? Do you have a URL to it?