Database cannot be accessed with second user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database cannot be accessed with second user

Author

Topic

dishnaw
Starting Member

4 Posts

Posted - 2008-06-18 : 14:23:17

Hi all,
I am acessing SQL server with two different users from my web site using .asp code, both users are created on Windows NT sever and SQL server.
The first user has only select permissions and the second user has permissions to insert, select, update, and delete data to few tables in the database.
Only the first user is connedted to the web site within IIS.
I have connection strings in my program using both users when necessary to insert data.

My question is, when I try to delete data using the second user I get following message. Please let me know if I can access web site with two users.

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]DELETE permission denied on object 'tblCustomText', database 'EdDataDB', owner 'dbo'.

Thanks a lot in advance.
Dishna

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 14:29:04

Yes you can use multiple users. The issue is in your application code as you aren't using the correct connection string (the one with write permissions).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dishnaw
Starting Member

4 Posts

Posted - 2008-06-20 : 11:35:14

Tara,
Thanks for your reply.
I use the following connection string to select data with the first user and similar connection string with a different user for the second time to insert data. Please let me know what other connection strings I can use. I use conn.execute (sSQL) to insert data, and sSQL contains insert SQL string.

connUsername = "test"
connPassword = "test1"

connstring ="DRIVER=SQL Server;SERVER=999.99.9.99,1833;UID=test;WSID=IIS;DATABASE=MydataDB;QueryLogFile=Yes;Trusted_Connection=Yes"

Set conn = server.CreateObject("ADODB.Connection")
conn.open connstring, connUsername, connPassword

Dishna

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-06-20 : 14:39:31

Can you log into EM and check that the user "test" does indeed have delete permissions on table tblCustomText? Within EM, go to database MydataDB and expand, select users then the user "test". Finally select the permissions button and scroll until you find tblCustomText. I'll bet $50 USD that the user "test" does not have the rights to delete. SQL Server never lies!!!

Terry

dishnaw
Starting Member

4 Posts

Posted - 2008-06-20 : 15:04:46

Terry,
Yes, it does have permissions to select,insert,update, or delete.
This permission is given to my second user who can do these operations. My first user who has only select permissions to this page. What I am thinking the problem with the second connection string( which I use to update and delete), is not setting right.
Even I set the second connection string with the second user, it only gets the connection string that the website is connected within IIS, which is the first user. I get the following error message when connect with the second user and insert, update, or delete.

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT permission denied on object 'tblCustomText', database 'EdDataDB', owner 'dbo'.

/maint/CustomText.asp, line 84

Thanks,
Dishna

dishnaw
Starting Member

4 Posts

Posted - 2008-06-23 : 18:16:31

Does anyone know why I am connected with the same user in ASP program even I made a different connection with the other user that has less previleges. I am connected with the IIS as the first user with less privileges and then within the program I close that connection and open the connection with the second user.
I close the first connection as follows:

connUpdate.close
set connUpdate = nothing

I do not understand why the connection does not close with the first user and let me connect with the second user.

Please help me to solve the problem. I appreciate it a lot.
Thanks,
Dishna

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-23 : 22:17:04

Use 'sp_who2 active' to find out user id connected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources