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:
1 2 3 4 5 6 7 8 9 |
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.
1 2 3 4 5 6 7 |
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:
1 2 3 4 5 6 7 8 |
USE [database_name] <- Again, users are database related, not server related.GOCREATE USER [username] FOR LOGIN [login_name] <- Login name and username can be differenceGOsp_addrolemember 'db_datareader','username' <- Role grantingGOsp_addrolemember 'db_datawriter','username' <- Role grantingGO |