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