"SELECT permission denied" problem

[daveigh]

but the permissions are complete. typically weird but true. i've checked all the permissions and there should be no problem. here is the line that generates the error:

oRs.Open "Select * From Users", Session("CONNECTIONSTRING"), adOpenKeyset,adLockOptimistic

where Session("CONNECTIONSTRING") has correct path for database. data are inserted correctly before being selected. what u guys think?

_______________CRYOcoustic_____________

 

[NoCoolHandle]

The easiest way to trouble shoot your issue is to turn on the SQL "Profiler" tool. You can watch the connect and see who and how you are connecting.

Quick thougths..
Without knowing what your connection string looks like makes fixing it from here very very difficut..
(can you tell how long the string is that I am holding up?)

Examples of your code make helping much easier

I am guessing that your issue is that you arn't allowing the webserver access to you database. (but who can tell)
Typically this is the iuser_ComputerName account, but it might be the asp_net account or some other account.

Again.. Show your connectionstrings (remove the servername if you feel paranoid and change your passwords if you use them) and or (best answer) Use the profier to find out why it isn't working.

Rob

 

[daveigh]

oRs.Open "Select * From Users", oConn, 2,2

where oConn = "DSN=pimdev"

i use DSN connections.

_______________CRYOcoustic_____________

 

[ArtieChoke]

Are you using a trusted connection for your DSN?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[daveigh]

Just an update.

The complete text of error:

SELECT permission denied on object 'Users', database 'ottoEPIMdev', owner 'dbo'.

here is the connection sting (if converted to a dsnless string connection):

set oConn = Server.CreateObject("ADODB.Connection")
oStrConn = "Provider=SQLOLEDB; Data Source=1.1.1.1; User ID=webuser; Password=pass; Initial Catalog=pimdev; NetworK Library=DBMSSOCN"
oConn.Open(oStrConn)


if checked in sql permissions, all the rights are given, before it only had the 1st two, and i checked all to just to make sure it will work, but it wont. =(



_______________CRYOcoustic_____________

 

[daveigh]

Artichoke,

i tried using dsnless connections. same error. what do you think?

_______________CRYOcoustic_____________

 

[NoCoolHandle]

When you go to the ODBC applet in COntrol panel (datasources or something like that) what type of authentication are you using?

My guess is that you use NT to validate your user. If this is the case you need to find out what NT account your webserver uses run tasks as and make sure they have permissions to do what ever in your db...

An easy way of doing this is to try

oRs.Open "select suser_sname()", oConn, 2,2
response.write "Login account to SQL=" &  rs(0).value & "<BR>"

When you get the value back (assuming your server will allow you to get a connection) check it's permissions in the database.

If it won't let you login you will need to go to the internet service manager and check which account the websever is running as...

HTH


Rob

 

[NoCoolHandle]

Ahh just saw an above response

set oConn = Server.CreateObject("ADODB.Connection")
oStrConn = "Provider=SQLOLEDB; Data Source=1.1.1.1; User ID=webuser; Password=pass; Initial Catalog=pimdev; NetworK Library=DBMSSOCN"
oConn.Open(oStrConn)

The plot thickens.....

Is 1.1.1.1 valid in QA when you login useing that set of credentials?


For some reason I think you need to use [blue]dbmssocn [/blue]. My memory is saying htis is case sensitive.

 

[daveigh]

I was just about to reply.

to answer your prior post :

Rob,

I checked it and it uses SQL Server authentication.

I access it on a remote desktop, the only authentication i can access is what i see in the enterprise manager. beyond that, i am not allowed. maybe ill talk to the system administrator...

ill try the code you gave me anyway.

to answer the next one:

1.1.1.1 is a valid ip for the SQL.

i'll change DBMSSOCN to lowercase.

______________CRYOcoustic_____________

 

[daveigh]

Changed DBMSSOCN to lowercase. same error.

Surely this one drains me. =(

______________CRYOcoustic_____________

 

[NoCoolHandle]

What do you see when you look at the connection using the Profiler tool?

Hold on this gets stranger..
[blue]
SELECT permission denied on object 'Users', database [red]'ottoEPIMdev'[/red], owner 'dbo'.

here is the connection sting (if converted to a dsnless string connection):

set oConn = Server.CreateObject("ADODB.Connection")
oStrConn = "Provider=SQLOLEDB; Data Source=1.1.1.1; User ID=webuser; Password=pass; Initial Catalog=[red]pimdev[/red]; NetworK Library=DBMSSOCN"
oConn.Open(oStrConn)
[/blue]

That isn't your database!

I think the folks that host your server have more than one account sharing a database.

You should try to look at the connection attempt however using "Profiler" it is a sql tool and can be used to easily troubleshoot issues like this. I would think that you won't have any issues with permissions as it is your database you want to audit.

Another thought.. Is the account that Enterprise Manager uses any different to the one your webpage uses.

Also did you ever try..
1. select suser_sname()
or other commands you might find usefull
2. select db_name()
3. select * from sysusers
4. select name from sysobjects where type='u' --(are these your tables?)

For some reason I think the issue is with whoever is hosting your database.
1 is who and how you authenticate...
2 what database did you get to
3 are these your users
4 are all your tables there?