Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to Create SQL Server Database with User?

Tags:

sql

sql-server

How (in SQL Server) to create a database and create a user and this user will be an admin on this database only with SQL script? I mean this user can not do/access any thing out side this database!

and is it possible to create user that has only can add, update/edit or delete data from tables and nothing else?

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

like image 804
Data-Base Avatar asked Jul 26 '10 14:07

Data-Base


People also ask

How do I create a SQL Server database?

Use SQL Server Management StudioRight-click Databases, and then select New Database. In New Database, enter a database name. To create the database by accepting all default values, select OK; otherwise, continue with the following optional steps. To change the owner name, select (...) to select another owner.

Can SQL take user input?

SQL*Plus has several commands that can be used to prompt the user for input, accept input from the user and store it in a variable, and then use that variable in a query.


2 Answers

Assuming that you are using windows auth with a login 'domain\user' that has already been created.

--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'

Alternatively to give the user admin over the database, replace the last two lines with.

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

CREATE DATABASE also has many options which you might need that can be found on BOL.

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

If you need to create a login also then you will need the following before creating the USER on your database.

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

--Windows Auth
CREATE LOGIN domain\user FROM WINDOWS; 
like image 162
Chris Diver Avatar answered Oct 07 '22 02:10

Chris Diver


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
like image 24
Vidar Nordnes Avatar answered Oct 07 '22 02:10

Vidar Nordnes