Set a login with username/password for SQL Server 2008 Express

Question

I would like to set a password and username for connecting to a server with SQL Management Studio 2008.

I set up SQL Server 2008 Express on a customer's computer to host a DB used by an Access 2007 app. The customer should not be able to access the DB or connect with SQL Management Studio.

How do I set up a login and remove any logins that allow a user to connect via Windows Authentication and without entering a username and password? I have not much experience with logins and controlling access.

Solution 1

The customer should not be able to access the DB or connect with SQL Management Studio.

That is not possible and anyone that tells you the opposite is selling snake oil. There is nothing you can do to prevent a local administrator from accessing his own database.

Solution 2

First you'll want to create your SQL Server account (though one might already exist)

• In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.

• Right-click the Security folder, point to New, and then click Login.

• On the General page, enter a name for the new login in the Login name box.

• Select SQL Server Authentication.

• Enter a password for the login.

• Select the password policy options that should be applied to the new login. In general, enforcing password policy is the more secure option.

• Click OK.

Taken from MSDN Article

Then you'll need to change the SQL Server authentication type to SQL Server from it's current settings (I'm guessing it's setup as both Windows Authentication and Server).

• In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

• On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

• In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

• To restart SQL Server from SQL Server Management Studio in Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

Taken from MSDN Article

That should be you setup with two accounts, the account setup using steps above for the application and an SA account which you will need to set to a secure password otherwise the user might still be able to access the database.

Comments

[db2]

This is correct. And if all else fails, just take the .mdf/.ldf files and attach them to a different instance of SQL Server with the appropriate login permissions. The best you could attempt to do is encrypt data or procedures, and there are ways around that too. So at best, you'll just irritate the DBA, which isn't great from a sales standpoint.