How to create a SQL Server database with a user?

Question

How (in SQL Server) to create a database and create a user, and this user will be the administrator of this database only with SQL script? I mean that this user cannot do / access any side of this database!

and is it possible to create a user who can add, update / edit or delete data from tables and nothing more?

I know how to do this in MySQL, but not sure how with SQL Server!

Answer 1

Assuming you are using windows auth with an already registered domain \ user.

--create the database
CREATE DATABASE NewDB

--create the user from the login
Use NewDB
CREATE USER [domain\user] FOR LOGIN [domain\user]

--To give user SELECT/UPDATE/INSERT/DELETE on all tables
EXEC sp_addrolemember 'db_datareader', 'domain\user'
EXEC sp_addrolemember 'db_datawriter', 'domain\user'

, , .

--To give admin permissions
EXEC sp_addrolemember 'db_owner', 'domain\user'

CREATE DATABASE , , BOL.

http://msdn.microsoft.com/en-us/library/ms176061.aspx

login, , USER .

--Using SQL Auth
CREATE LOGIN loginname WITH PASSWORD = 'passw0rd';

--Windows Auth
CREATE LOGIN domain\user FROM WINDOWS; 

Answer 2

DECLARE
  @DatabaseName AS NVARCHAR(128),
  @Username AS NVARCHAR(128),
  @Password AS NVARCHAR(128),
  @SQL AS NVARCHAR(MAX)

SELECT
  @DatabaseName = 'YourDatabaseName',
  @Username = 'Username',
  @Password = 'Password'

SET @SQL = 'CREATE DATABASE [' + @DatabaseName + ']'
EXEC (@SQL)

SET @SQL = 'CREATE LOGIN [' + @Username + '] WITH PASSWORD = ''' + @Password + ''''
EXEC (@SQL)

SET @SQL = 'USE ' + @DatabaseName
EXEC (@SQL)

SET @SQL = 'CREATE USER [' + @Username + '] FOR LOGIN [' + @Username + ']'
EXEC (@SQL)

EXEC sp_addrolemember 'db_owner', @username