We backup the Northwind database
1. In the Object Explorer panel, right-click on the Backup Devices folder and select Back up a Database.
2. In the window that will open, select the Northwin database in the Database option.
3. Make sure the backup type is FULL.
4. In the Destination panel / list, delete the specified backup device by selecting and then clicking the Remove button.
5. Click the Add button, and in the window that opens, click Backup Device, and select Full Backup. Then click the OK button.
6. In the Select a page panel, in the Back up a database window, select Media Options.
7. Select Overwrite all existing backup sets.
8. Generate the script (instructions) for this configuration and save it to the backups.sql file.
9. Then click the OK button to save the configurations. In the notification window that appears, announcing ‘The database backup completed successfully’, click the OK button.
10. Realize points 1-9 for the type of backup Transaction Log by naming and selecting the BackupLogs device.
11. Realize points 1-9 for the Differential backup type and by selecting the BackupDiff device.
12. Realize points 1-9 for the Transaction Log backup type and by selecting the BackupLogs device. In the Backup Options option, change the name to Northwind – Transaction Log2
13. Save the changes made to the Backups.sql file, which contains the script of the strategy required by the Northwind Administrator.
Creating SQL Serve Login
In this workshop we will see step by step the creation of a Login using SSMS and T-SQL query.
Before we start creating a new login, let’s first look at the list of logs we already have in SSMS, which is located in the Object Explorer: Security -> Logins panel:
Creating login via SSMS
To create a new login via SSMS, right-click on the Security folder and select New Login …
After clicking on this option, the following window will appear, which has previously selected the login option using Windows credentials – Windows Authentication. We change this option by selecting the SQL Server Authentication option, where other fields are then activated to be completed below:
Login Name -> a unique name must be specified for the new SQL Server Login to be created. This name will be the one that will be used later to log in to the system.
Password-> A new login password must be specified.
Enforce Password Policy -> if you check this option, the password will need to meet the standard SQL Server Pasword policies.
Enforce Password Expiration -> Password will expire after a certain period.
Ser User Muast Change Password at New Login -> When the user is logged in for the first time with these credentials they will be prompted to change their password.
In the figure below you will see that we have created the login ‘TIK1920’ with any password and we have un-checked the options for password controls.
We will then need to fill in two more areas for this login:
Default Database -> the name of the default database to be assigned to this user should be selected here. We are currently selecting the University database.
Fa Default Language -> These select the language in which the user will work. By default it is English, and so will we.
We then go to the Server Roles page, where all the roles of the server are displayed and select which role the new login will be connected to. The role of the Public server is pre-selected.
We move on to the User Mapping page. Use this page to select the database and assign database roles to this user. Since we selected the University database as the default database, Logini automatically linked the user to this database. We add that the user also connects to the pubs database.
At the bottom is the list of database roles, which are explained in the lecture on their use and functionality.
For this login we will select the roles: Public and db_Owner for the database University and Public for the database Pubs.
On the Secuables page, configurations are performed to select objects. Click on the Search button … and in the window that opens, select The Server … and then click OK.
As soon as you click on the OK button, the following Explicit field will be filled with all the rights that can be assigned or denied to users. At the moment we will not make any changes.
On the Status page, we have two options:
Permission to connect to database Engine -> allow logging in database engine, if so check Grant, otherwise select deny.
Login -> If you want the user to log in to Database Manager, you need to check Enabled, otherwise Disabled.
Before closing the window for creating the new login, we generate the scripting of this configuration by clicking on the Script button, and then clicking the OK button. Below is the entire T-SQL instruction for creating the TIK1920 login.
1. USE [master]
3. CREATE LOGIN [TIK1920] WITH PASSWORD = N’tik1234 ‘, DEFAULT_DATABASE = [University], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
5. USE [7s]
7.CREATE USER [TIK1920] FOR LOGIN [TIK1920]
9. use [University];
11. use [pubs];
13. USE [University]
15. CREATE USER [TIK1920] FOR LOGIN [TIK1920] 16. GO
17. USE [University]
19. ALTER ROLE
ADD MEMBER [TIK1920]
Meanwhile, in the Security folder -> Logins, the newly created login has been added to the list.
We try to log in with this user. Where you need to select the SQL Server Authentication option as the authentication type and then set the criteria specified above.
The moment you log in, we will see in the upper part of Object Explorer the name of the login with which we are authenticated.
When a user opens an editor – New Query – it is logged directly into the University database, as this was the database assigned to you as Default.
We will also see that the user is shown the entire list of databases.