Locking a SQL Server Database with PHP

Question

I'm wanting extra security for a particular point in my web app. So I want to lock the database (SQL Server 2005). Any suggestions or is this even necessary with SQL Server?

Edit on question:

The query is failing silently with no errors messages logged, and does not occur inside of a transaction.

Final Solution:

I never was able to solve the problem, however what I wound up doing was switching to MySQL and using a transactional level query here. This was not the main or even a primary reason to switch. I had been having problems with SQL Server and it allowed me to have our CMS and various other tools all running on the same database. Previous we had a SQL Server and a MySQL database running to run our site. The port was a bit on the time consuming however in the long run I feel it will work much better for the site and the business.

Answer 1

I suppose you have three options.

1. Set user permissions so that user x can only read from the database.

2. Set the database into single user mode so only one connection can access it

   sp_dboption 'myDataBaseName', single, true

3. Set the database to readonly

   sp_dboption 'myDataBaseName', read only, true

Answer 2

I am needing to insert data into the database, but in testing the database didn't insert when two people were trying to insert at once.

Answer 3

@jtyost2: is this the same or different question?

Some thoughts:

Did both inserts fail with an error message, or did they silently fail? Are the insert statements inside a SQL transation.

Have you used SQL Profiler to see what SQL code and traffic is hitting the database?

Answer 4

@jtyost2: Could you please provide us with some sample code?

Answer 5

didn't insert when two people were trying to insert at once.
What is the error message you recieve on failure?

Answer 6

I never was able to solve the problem, however what I wound up doing was switching to MySQL and using a transactional level query here. This was not the main or even a primary reason to switch. I had been having problems with MSSQL and it allowed me to have our CMS and various other tools all running on the same database. Previous we had a MSSQL and a MySQL database running to run our site. The port was a bit on the time consuming however in the long run I feel it will work much better for the site and the business.