Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help - executing SQL on SQL Server from ASP

Status
Not open for further replies.

scm0za

Programmer
Joined
Feb 8, 2004
Messages
2
Location
GB
Hi all if anyone has any ideas can u please help,

I have been stuck on ths issue for over a week. Here goes I have created a website using ASP and SQLServer. Users are all able to execute STored procedures on the site but as soon as they try to execute SQL from a ASP page they get the following error:

HTTP 500.100 - Internal Server Error - ASP error

-----------------------------------------------

Microsoft OLE DB Provider for SQL Server (0x80040E09) SELECT permission denied on object 'tblPlatform', database 'Server_management', owner 'dbo'.

/serrver_management/searchKell.asp, line 571

I do not get this error, every other user does! ALthough I do not have a seperate SQLServer login I use the domain LSL-UK\Domain Users.)eg I am LSL-UK\SmithC, everyone else is LSL-UK\username

I have tried the following conn strings and get the same problem:

1)
strConnection = "Provider=sqloledb;User ID=sa;Password=dbo;"
strConnection = strConnection & "Initial Catalog=Server_management;Data Source=cpmspodb5w;trusted_Connection=yes;"

2)
strConnection = "Provider=SQLOLEDB;Integrated Security=SSPI;"
strConnection = strConnection & "Initial Catalog=Server_management;Data Source=cpmspodb5w;Connect Timeout=600"

I have a feeling it is something to do with IIS. Could it be because I am an IIS administrator and no one else is?

Any Help would be greatly appreciated.

Thanks


 
I would set up a user in SqlServer and give that user EXEC permissions to your stored procedures and in this case SELECT permissions to tblPlatform. Then use that user in your connections string.
 
I have created a db user under for my database called Domain USers and Login Name LSL-UK\Domain Users. I have given this group every permissioin I think possible yet they still get an error when the SQL (in the ASP) executes a SELECT statement on the table 'tblPlatform' although I have specifiaclly given this group SELECT permissions on this table. I seem to be ablt to execute this SELECT statement but no one else. This is the error Message:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E09)
SELECT permission denied on object 'tblPlatform', database 'Server_management', owner 'dbo'.
/server_management/searchKell1.asp, line 571.

Can anybody help?
 
I'm betting that what is happening is that IIS is not using windows authentication. Since it is not using Windows Authentication it automatically executes as the local IUSR_MachineName account, which doesn't have permision for SQL Server. I'm thinking that if you were t turn on authentication for IIS (and set it up for domain accounts also) that you may have better luck. Either that or include the default IS user as an SQL Server user also.

What confuses me is the permissions issue when you use the sa account. You should not be getting an error with a hardcoded username/password pair in one place and not another. IIS or no IIS, this should work exactly the same, especially sincew it is being executed from the same location no mater who accesses it.

I'm not sure tat the above solution will help (due to the second paragraph showing other odities tat aren't covered in the first) but it might be worth a try. You may also want to check that everyone has read/write capability, it sounds like they have connect permissions but not read. Otherwise the connection would have failed, not the SELECT.

-T

01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top