=====Overview=====
In SQL Server, unlike Oracle, you have 2 sets of accesses to the database if I can say so:
* Login - Access to the server in general
* Username - Access to a specific database
A login can have access to a particular database or all databases, depending on its rights. Because of that, generally a DBA has to create a LOGIN for each user who wants to use the database and a username per specific database.
The DBA can do that, either via the GUI (SSNS) or via scripts:
=====Creation of a database=====
The creation of the database in MS SQL is straight forwards:
CREATE DATABASE [database_name]
ON ( NAME = N'database_name_data_01',
FILENAME = N'D:\data\database_name\database_name_data_01.mdf',
SIZE = 128MB, MAXSIZE = UNLIMITED , FILEGROWTH = 128MB )
LOG ON ( NAME = N'database_name_log_01',
FILENAME = N'D:\logs\database_name\database_name_log_01.ldf',
SIZE = 128MB, MAXSIZE = 8192MB, FILEGROWTH = 128MB )
COLLATE SQL_Latin1_General_CP1_CI_AS
;
After a database is created, DBA can create a login and then username. Login is created on server level, where user is created on database level:
=====Login Creation=====
Login is single access to the server, usually it does not allow you access to any data. Login are used to ONLY connect you to the server.
CREATE LOGIN [tainops]
WITH PASSWORD=N'password',
DEFAULT_DATABASE=[database_name],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
;
GO
=====User Creation=====
After the Login is created, we can create a user associated with that login as follows:
USE [database_name] <- Again, users are database related, not server related.
GO
CREATE USER [username] FOR LOGIN [login_name] <- Login name and username can be difference
GO
sp_addrolemember 'db_datareader','username' <- Role granting
GO
sp_addrolemember 'db_datawriter','username' <- Role granting
GO