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.SelectComm
Query = SqlDataAdapter1.Fill(DataS
SqlConnection1.Close()
End Function
My SQL connection string:
The code works perfectly when I use my Admin account
Me.SqlConnection1.Connecti
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(DataS
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.SelectComm
Query = SqlDataAdapter1.Fill(DataS
SqlConnection1.Close()
End Function
My SQL connection string:
The code works perfectly when I use my Admin account
Me.SqlConnection1.Connecti
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(DataS
Visual Basic.NET
Last Comment
toddhd
try this:
'does the dataset has the table?
Query = SqlDataAdapter1.Fill(DataS
Function Query()
SqlConnection1.Open()
Dim Finder as string = ("SELECT * FROM MyTable WHERE user_name= 'MyName'")
SqlDataAdapter1.SelectComm
Query = SqlDataAdapter1.Fill(DataS
SqlConnection1.Close()
End Function
'does the dataset has the table?
Query = SqlDataAdapter1.Fill(DataS
Function Query()
SqlConnection1.Open()
Dim Finder as string = ("SELECT * FROM MyTable WHERE user_name= 'MyName'")
SqlDataAdapter1.SelectComm
Query = SqlDataAdapter1.Fill(DataS
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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???
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.
- 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
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
USE YourDatabase
GRANT SELECT ON YourTable TO YourReadOnlyUser
Paul
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.
("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.
ASKER
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.
ASKER
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 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?
SELECT * FROM dbo.MyTable WHERE dbo.MyTable.user_name= 'MyName'