SQL SERVER Express edition is read only

[29021972]

Hi.
I installed SQL 2005 express, and I can only look at data. Whenever I try to do an update, the systems says the data is readonly.
I do have adminitrator rights, the database files are not read only.
Is there anyone that knows how to solve this problem?

 

[mrdenny]

Sounds like you are connecting via a non-sysadmin account, or the filegroup you are trying to update data in is set for readonly. Please post the exact error message that you are getting.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[29021972]

Hi,

The message I get is ...unfortunately in dutch. Translated it says: the recordset cannot be updates. I am admin. I checked if the filegroup was readonly, it is not.

The strange thing is that this messages only apears when I try to connect to the database remotely. If I try it on the server where I installed SQL-server, everything works fine.
So the data is only readonly when I connect remotely....but I am able to view the data.

... I tried ecery suggestion I found on the internet ... no effect.

 

[kmkeshav]

I think you have to do something with Surface Area Configuration. You may have to enable some settings that allow execution of remote queries.

-Keshav

 

[29021972]

... unfortunately I already tried that.
The "remote connections" is on "Local and remote connections".
.. using both TCP/IP and named pipes

 

[mrdenny]

Are you sure that you are logging in with an account that has admin rights when you connect from remote? SQL doesn't care if you are local or remote with reguard to if it will let you update the data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[29021972]

I use the same login: sa, with the same password.
I did find out something interesting today. Maybe somebody can help me further knowing this:

I always try to connect remotely using ADO or with a Ms Access ADP-file. Today I made an ODBC connection ... it worked! I was able to update the data remotely using the ODBC connection!

So it looks like the problem only occurs when I use ADO (ADO.NET, but some of my code still uses ADO, some even DAO as well) or an Access .ADP files.

... but I really need to connect using ADO... Anyone?

 

[mrdenny]

That would be your problem then. There was probably some security within the Access ADP file that was stopping you. Perhaps the ADO recordset's were being opened in read only mode.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[29021972]

Some strange things happen. At this moment I am able to update the data using ADO.NET. Thatis something new! I don't know what I did wrong in the past, but it works now.
(maybe I used the OleDb class instead of the sqlClient, I don't know).

Thanks for thinking with me mrdenny.

One problem left:

How can I configure the security within the Access ADP file to be able to update data from there?



MCAD(2004)

 

[mrdenny]

No idea. I haven't looked as MS Access in 7 years. Check with the folks in the MS Access forums.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com