If you have created a database but don't need it anymore, you can delete it. It is important to know, regardless of how you create a database, whether using SQL Server Management Studio, code in the query window, or the Command Prompt, every database can be accessed by any of these tools and you can delete any of the databases using any of these tools.

As done with creating a database, every tool provides its own means.

To delete a database in SQL Server Management Studio, in the Object Explorer, expand the Databases node, right-click the undesired database, and click Delete. A dialog box would prompt you to confirm your intention. If you still want to delete the database, you can click OK. If you change your mind, you can click Cancel.

To delete a database in a Query window, use the DROP DATABASE expression followed by the name of the database. The formula used is:

DROP DATABASE DatabaseName;

Before deleting a database in SQL, you must make sure the database is not being used or accessed by someone else or by another object.

A namespace is a group of "things" where each thing has a unique name:

Notice that there are various types of objects within a namespace. For example, inside a company, each department has a unique name. Because two companies are independent, they can have departments that have the same name inside each company.

To organize its own items, a namespace can have other namespaces inside. That is, a namespace can have its own sub-namespaces, just like a company can have divisions.

As mentioned already, a namespace can have objects inside. To further control and manage the objects inside of a namespace, you can put them in sub-groups called schemas. Therefore, a schema (pronounced skima) is a group of objects within a namespace. This also means that, within a namespace, you can have as many schemas as you want:

Notice that, just like a namespace can contain objects (schemas), a schema can contain objects also (the objects we will create throughout our lessons).

To manage the schemas in a namespace, you need a way to identify each schema. Based on this, each schema must have a name. In our illustration, one schema is named Schema1. Another schema is named Schema2. Yet another schema is named Schema_n.

A schema is an object that contains other objects. Before using it, you must create it or you can use an existing schema. There are two types of schemas you can use, those built-in and those you create. When Microsoft SQL Server is installed, it also creates a few schemas. One of the schemas is named sys. Another is called dbo.

The sys schema contains a list of some of the objects that exist in your database system. One of these objects is called databases (actually, it's a view). When you create a database, its name is entered in the databases list using the same name you gave it.

To access the schemas of a database, in the Object Explorer, expand the Databases node, expand the database that will hold or own the schema, and expand the Security node.

To visually create a schema, right-click Schemas and click New Schema...

This would open the Schema - New dialog box. In the Schema Name text box, enter a one-word name. Here is an example:

After providing a name, you can click OK.

The formula to programmatically create a schema is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

<schema_name_clause> ::=
    {
    schema_name
    | AUTHORIZATION owner_name
    | schema_name AUTHORIZATION owner_name
    }

<schema_element> ::= 
    { 
        table_definition | view_definition | grant_statement |
        revoke_statement | deny_statement 
    }

The most important part is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

In this case, start with the CREATE SCHEMA expression and add a name to it. Here is an example:

1> CREATE SCHEMA PrivateListing;
2> GO
1>

The other parts deal with issues we have not studied yet.

Inside of a schema, two objects cannot have the same name, but an object in one schema can have the same name as an object in another schema. Based on this, if you are accessing an object within its schema, you can simply use its name, since that name would be unique. On the other hand, because of the implied possibility of dealing with objects with similar names in your server, when accessing an object outside of its schema, you must qualify it. To do this, you would type the name of the schema that contains the object you want to use, followed by the period operator, followed by the name of the object you want to use. From our illustration, to access the Something1 object that belongs to Schema1, you would type:

Schema1.Something1

As mentioned already, when Microsoft SQL Server is installed, it creates a schema named dbo. This is probably the most common schema you will use. In fact, if you don't create a schema in a database, the dbo schema is the default and you can apply it to any object in your database.

A permission is an action that a user is allowed to perform, or is prevented from performing, on a database or on one of its objects.

Many server operating systems and database environments use the word "right" for permission. In our lessons, we will use both words interchangeably. That is, for the rest of our lessons, the words "right" and "permission" will mean the exact same thing.

Microsoft SQL Server provides various levels of security and therefore permissions are managed on different levels.

First of all, there are two categories of people who use a database: the database developers and the regular users. As a consequence, the are two categories of permissions you will deal with.

In order to do something on the server or one of its objects, a user must be given the permission. This is also referred to as granting a permission. To grant permissions, the account you are using must have the ability to do so. This means that, before granting permissions, you must log in with an account that has its own right permissions. You can grant permissions visually or with code.

To visually grant one or more permissions on the server, in the Object Explorer, right-click the name of the server and click Properties. In the left frame of the Server Properties dialog box, click Permissions. In the Logins or Roles list, click the name of the user. In the bottom list, use the options in the Grant column:

The basic formula to programmatically grant one or more permissions is:

GRANT Permission TO Login

You start with the GRANT keyword followed by the name of the permission. After the permission, type TO, followed by the login name you want to grant the permission to. Here is an example that gives operez to create a database on the server:

USE master;
GO
GRANT CREATE ANY DATABASE
TO operez;
GO

If you want to grant more than one permission, separate their names with commas. Here is an example:

GRANT CREATE ANY DATABASE, SHUTDOWN
TO operez;
GO

If you want to grant the same permission(s) to more than one account, list them, separated by commas. Here is an example:

GRANT CREATE ANY DATABASE, ALTER ANY LOGIN
TO pkatts, gdmonay;
GO

1. Server: In the Object Explorer, right-click the top node (the name of the computer) and click Properties...
2. Server: In the left frame, click Permissions
3. Server: In the Logins or Roles list, click DomainName\pkatts
4. Server: In the Permissions list, in the Grant column, click the check box that corresponds to Create Any Database.
   Still in the Grant column, make sure the check box of Connect SQL is checked
    
   
5. Server: Still in the Permissions for pkatts section, in the Grant column, click the check boxes that correspond to Alter Any Connection, Alter Any Database, and Alter Any Login
    
   
6. Server: In the Logins or Roles list, click DomainName\gmonay
7. Server: In the Permissions list, in the Grant column, click the check box that corresponds to Create Any Database.
   Make sure the check box of Connect SQL is checked
8. Server: In the Logins or Roles list, click DomainName\rkouma
9. Server: In the Permissions list, in the Grant column, click the check box that corresponds to Create Any Database.
   Make sure the check box of Connect SQL is checked
10. Server: In the Logins or Roles list, click DomainName\operez
11. Server: In the Permissions list, in the Grant column, click the check box that corresponds to Create Any Database.
    Make sure the check box of Connect SQL is checked
12. Server: Click OK

To primary permission a person needs in Microsoft SQL Server is to be able to connect to the server. This permission is called CONNECT. This is also the default permission. After all, if a person cannot establish a connection to the server, she cannot use the database. When you create a new user account, it is automatically given the right to connect to the server. Otherwise, you can deny it if you want.

1. Each Student: In the Object Explorer, right-click Databases and click New Database...
    
   
2. pkatts: Type MotorVehicleAdministration1
3. gmonay: Type MotorVehicleAdministration2
4. rkouma: Type MotorVehicleAdministration3
5. operez: Type MotorVehicleAdministration4
6. Each Student: In the Owner dialog box, click <default> and type the domain name, followed by \, and followed by your login name (you can also click the browser button on the right side of Owner to locate and select the desired username)
7. Each Student: In the Database Files section, click the box under the Initial Size column header, click the up arrow of the spin button and increase its value to 5
8. Each Student: Scroll to the right side and, under the Path header, notice the location of the file
9. Each Student: To open the code editor, in the Object Explorer, right-click the name of the server and click New Query
    
   
10. Each Student: In the empty window, type:

    CREATE DATABASE RealEstate1;
    GO

11. Each Student: To execute the statement, press F5

As opposed to granting rights, you can prevent a user from doing something on the server, on a database, or on an object. This is referred to as denying a permission.

To visually deny one or more permissions, in the Object Explorer, right-click the name of the server and click Properties. In the left frame, click Permissions. In the Logins or Roles list, click the name of the user. Use the options in the Deny column.

The basic formula to programmatically deny one or more permissions is:

DENY Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n

Here is an example:

DENY CREATE ANY DATABASE
TO rkouma;
GO

1. Server: In the Object Explorer, right-click the name of the computer and click Properties...
2. Server: In the left frame, click Permissions
3. Server: In the Logins or Roles list, click operez
4. Server: In the Permissions list, in the Deny column, click the check box that corresponds to Create Any Database
    
   
5. Server: Click OK
6. Server: In the Object Explorer, right-click RealEstate1 and click Properties
7. Server: In the left frame, click Permissions
8. Server: In the Users or Roles section, click Orlando
9. Server: In the Permissions for Orlando section, in the Deny column, click the check box that corresponds to Connect
    
   
10. Server: Click OK
11. operez: In the Object Explorer, expand the Databases node
12. operez: Click the + button of MotorVehicleAdministration. Notice that you can expand it
13. operez: Still in the Object Explorer, click the + button of RealEstate1.
    Notice that you receive an error
     
    
14. operez: Click OK on the message box
15. Server: In the Object Explorer, right-click MotorVehicleAdministration and click Delete
     
    
    
16. pkatts: Type MotorVehicleAdministration1
17. gmonay: Type MotorVehicleAdministration2
18. rkouma: Type MotorVehicleAdministration3
19. operez: Type MotorVehicleAdministration4
20. All Computers: In the Delete Object dialog box, click OK
21. Server: Return to PowerShell
22. Server: Type USE Master; and press Enter
23. Server: Type GO and press Enter
24. Server: To delete a database, type the following code and press Enter after each line:
    

    DROP DATABASE RealEstate1;
    GO

    
25. Server: Type Quit and press Enter
26. Server: Type Exit and press Enter
27. Server: Back in the SQL Server Management Studio, click inside the Query window and press Ctrl + A to select everything
28. Server: To delete a database, type the following code:

    drop database [beauty salon];
    GO

29. Server: Press F5 to execute the statement
30. All computers: Close Microsoft SQL Server
31. All Computers: When asked whether you want to save, click No

There are many issues you need to keep in mind in order to rightfully manage permssions. This is because permissions are somehow interconnected. This means that granting one permission may not work if another right is not given or is denied to the same user.

There are many permissions in Microsoft SQL Server. Some permissions are used regularly and are of primary importance:

• CONNECT: Obviously the primary right you need to give a user is the ability to connect to a Microsoft SQL Server database. If you want to permanently or temporarily block access of the server to a user, you can deny the Connect permission
• CREATE ANY DATABASE: By default, users are able to create new databases on the server as long as they have access to it (to the server). The CREATE ANY DATABASE permission allows a user to create a new database. If you want a user to only be able to use existing databases created by other people such as the database administrator(s), you should deny this right
• ALTER ANY DATABASE: Even if you prevent a user from creating new databases, she can still change something in the existing databases. To prevent such actions, you should deny this right
• ALTER ANY LOGIN: This permission allows a user (the user who receives this right) to change the login account of another user. This right should be granted only to database administrators

Besides granting or denying permissions to an account, you can give an account the ability to grant or deny permissions to other accounts. To do this visually, open the Database Properties for the database you want to work on. In the Users or Roles section, select the user. In the Persmissions, use the check boxes in the With Grant column.

The formula to programmatically give an account the ability to grant or deny permissions to other accounts is:

GRANT Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n
WITH GRANT OPTION

This follows the same formula as the GRANT right we saw earlier. You must just add the WITH GRANT OPTION expression.

Consider the following SQL statement:

DENY CREATE ANY DATABASE
TO rkouma;
GO

When this code has been executed, if the TO user logs in and tries creating a database, he would receive an error:

Revoking a permission consists of either denying a permission that was previously granted or granting a permission that was previously denied. To visually do this, open the Properties dialog box of the database (or the object) on which the permission was managed.

To programmatically revoke a permission, the formula to follow is:

REVOKE [ GRANT OPTION FOR ] <permission> [ ,...n ]  
    { TO | FROM } <database_principal> [ ,...n ] 
        [ CASCADE ]
    [ AS <database_principal> ]

<permission> ::=  permission | ALL [ PRIVILEGES ]

<database_principal> ::= Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 
    | Database_user_mapped_to_certificate 
    | Database_user_mapped_to_asymmetric_key 
    | Database_user_with_no_login

Start with the REVOKE keyword followed by the permission(s). This is followed by either TO or FROM and the login name of the account whose permission must be managed. Here is an example:

/*
DENY CREATE ANY DATABASE
TO rkouma;
GO
*/

REVOKE CREATE ANY DATABASE
TO rkouma;
GO

Revoking a permission doesn't give that same permission. Imagine a user with a newly created account didn't have the permission to create new databases. If you deny that person the ability to create new databases, that denial becomes effective. If you revoke the permission, you are asking the server to restore the status of that person with regards to that particular right. That doesn't give that user the permission. The above code doesn't give the user the right to create new databases. If you want the user to have a right, you must explicitly grant the permission. Consider the following code:

REVOKE CREATE ANY DATABASE
TO rkouma;
GO

GRANT CREATE ANY DATABASE
TO rkouma;
GO

This restores the user's denial for creating new databases, then grants the permission to that user. This time, the user has the right to create new databases.

1. On the C:\ drive or any drive on the computer, create a folder named National Health Care Management
2. Using PowerShell, create a database named nhcm1 (which stands for National Health Care Management 1). Store its .mdf and its .ldf files in the National Health Care Management directory

• Start Microsoft SQL Server and login
• By writing SQL code in a Query window, create a database named MetrEC (this stands for Metropolitan Area Electric Company)