Sulaymaniyah Polytechnic University

Technical College of informatics

Subject:DBMS

Create Login and User

Prepared by
Dana A. Salh
1
 SQL Server CREATE USER
The SQL Server CREATE USER statement allows you to add a user to the current database.
The following shows the basic syntax of the CREATE USER statement:

• First, specify the name of the user after the CREATE USER keywords.
• Second, specify the login_name for the user. The login name must be valid on the
server. To create a login, you use the Create Login statement.

2
 SQL Server CREATE LOGIN
Before creating a user that accesses the databases in an SQL Server, you need to
follow these steps:
• First, create a login for SQL Server.
• Second, create a user and map the user with the login.

To create a login, you use the CREATE LOGIN statement. The following shows the basic
syntax of the CREATE LOGIN statement:

The password is case-sensitive. It needs to be between 8 and 128 characters and

can include a-z, A-Z, 0-9, and most non-alphanumeric characters.

3
 SQL Server CREATE LOGIN statement example
The following statement creates a new login called bob with the password Ebe2di68.

The login bob can log in to the SQL Server, and view the database names, but cannot
access any databases.

4
 SQL Server CREATE USER statement
First, create a new login called alex with the password 'Uvxs245!':

Second, switch the current database to the BikeStores:

Third, create a user with the username alex that uses the alex login:

The user alex can connect to SQL Server using the alex login’s password and
accesses the BikeStores database. However, the user alex cannot access any
tables and other database objects in the BikeStores database.

5
SQL Server GRANT
Once creating a user using the CREATE USER statement, the user doesn’t have any
permissions on the database objects like tables, views, and indexes.

To allow the user to interact with the database objects, you need to grant
permissions to the user.
The GRANT statement allows you to grant permissions on a securable to a principal.
• A securable is a resource to which the SQL Server authorization system regulates access.
For example, a table is a securable.
• A principal is an entity that can request the SQL Server resource. For example, a user is a
principal in SQL Server.

6
 GRANT Syntax
Here’s the basic syntax of the SQL Server GRANT statement:

In this syntax:

• First, specify one or more permissions after the GRANT keywords. If you have multiple
permissions, you need to use a comma to separate the permissions.
• Second, specify a securable after the ON keyword.
• Third, specify the principal after the TO keyword.

7
SQL Server GRANT example
First, create the HR database with a People table:

8
 SQL Server GRANT example cont.
Second, create a login with the name peter: Third, create a user peter in the HR database
for the peter login:

Fourth, connect the SQL Server using the peter user. And
you’ll see that the user peter can access the HR database
but cannot view any tables.

Fifth, switch to the system administrator connection and grant

the SELECT permission to the user peter on the People table:

Sixth, the user peter can see the People table and select data from
it. For example:

9
 Some Issues
However, the user peter cannot insert data into the People table:

SQL Server issues the following errors:

Similarly, the user peter also cannot delete data from the People table:

Error:

10
 Solve Previous Issues
To grant the INSERT and DELETE permissions on the People table to the user peter:

The user peter‘s connection and insert a new row into the People table:

Now, the user peter can insert data into and delete data from the People table.

What about Update?

11
 Change Login Info.
How to change login information in SQL Server?

12
 SQL Server REVOKE
The REVOKE statement removes previously granted permissions on a securable from a
principal.
The following shows the syntax of the REVOKE statement:

In this syntax:
First, specify one or more permissions in the REVOKE clause.
Second, specify a securable in the ON clause.
Third, specify a principle in the FROM clause.

13
 SQL Server REVOKE statement example
To follow the example, you need to complete the GRANT statement example that
creates the user peter and grant the SELECT, INSERT, and DELETE permissions on
the People table to the user peter.

Connect the SQL Server using the system administrator (SA) account and use
the REVOKE statement to remove the DELETE permission on the People table from
the user peter:

Remove the SELECT and INSERT permissions on the People table from the user peter:

14
 SQL Server Backup
• A backup is an image of that database at the time of the full backup.
• When you back up a database, you copy it to a backup device such as a disk. Later,
you can use the backup files to restore the database to its original state.

Why backup
Typically, you back up a database to restore its original state just before a disaster such as:
•Hardware failure
•Database corruption
•User-errors
Where to store the backups
Ideally, you should store the backup on a separate device that is not the same as the SQL
Server.
Which databases need backing up
You should back up both user databases and system databases

Backup Types
•Full backup
•Differential backup
•Transaction log backup
15
 Full Backup
A full database backup backs up the whole database. It includes the following data:
•The metadata of the database such as name, creation date, database options, file paths,
and so on.
•The used data pages of every data file.

To create a full backup of a database, you use the BACKUP DATABASE statement with the
following syntax:

16
 Full Backup Example
First, create the HR database with one table called People and insert four rows into it:

17
 Full Backup Example
Second, use the BACKUP DATABASE statement to back up the HR database to
the hr.bak file located in the D:\backup\ directory:

Third, use the RESTORE HEADERONLY to read from the backup file:

18
 Perform multiple full backups in one file
SQL Server allows you to store multiple backups in one backup file. To do that, you need to
use the NOINIT option in the WITH clause. For example:

First, insert a new row into the People table:

Second, perform a second full backup into the same backup file:

19
 Restore a database from a full backup
The following shows the syntax of the RESTORE DATABASE statement:

Restoring from the first full backup

Second, restore the HR database from the first full backup:

20
 Restoring from the first full backup Cont.

21
 Restoring from the second full backup
First, switch to the master database and drop the HR database:

Second, restore the HR database from the second full backup:

22
 Restoring from the second full backup Cont.

23