Create a user for SQL Server 2008?

Question

I'm using this code to create a new user.

/****** Object:  User [primebox] ******/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'primebox')
CREATE USER [primebox] FOR LOGIN [primebox] WITH DEFAULT_SCHEMA=[primebox]
GO

and I got the following error when I try to create this specific user.

Msg 15007, Level 16, State 1, Line 1
'primebox' is not a valid login or you do not have permission.

The part that I don't understand is, I can create others users but this one in particular looks invalid

I tried to rename my database and got this error

Unable to rename primeboxs. (ObjectExplorer)

Rename failed for Database 'primebox'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

My guess is that the problem is the database name... but I got the code from another database (I'm creating a 'wanna be' backup) with the same user and database names. So I'm in a corner here... Any help will be a LOT appreciated =)

PS: My computer name is box, windows 7. Using SQL Server 2008

Answer 1

Logins are different things than users, here is how to create a new LOGIN.

1. Right click SQL Server Management Studio and choose -> Run as adminstrator.

2. Login normally with the default Windows Authentication.

3. Choose Databases -> System Databases -> master. Click "New Query" button upper left.

4. Paste this query in there. (for more info SQL login options see here):

   CREATE LOGIN youruser WITH PASSWORD = 'yourpassword'
   

5. You should get this response:

   Command(s) completed successfully.
   

6. Check to make sure it got added to dbo.syslogins

   select * from master.dbo.syslogins
   

7. Close and restart SQL Management server (as administrator) and restart SQL Server itself by going to the SQL Server Configuration panel and clicking restart.

8. In the object explorer click the main EL-PC\SQLEXPRESS -> Security -> Logins.

9. Your newly created login should be there, right click and go to properties, you have to grant that user permission to visit the default database, and set a default database.

10. Close SQL Server and login again with the SQL Server Authentication option and username/password. You should now be logged in with your new user.

How to create a new user

1. Start SQL Server Management Studio as administrator.
2. Expand the database which you want to create the new database user.
3. Right click the "Security" folder choose "New User", if the option isn't available you probably clicked the server's security folder.
4. Add the name, and configurations, and click OK.

Answer 2

In SQL Server, users are different to logins.

To create a user for a database, you first have to create a login for the server using the create login syntax