Skip to content

lesson-3-deleting-database-objects.md

Latest commit

 

History

History
98 lines (71 loc) · 2.73 KB

lesson-3-deleting-database-objects.md

File metadata and controls

98 lines (71 loc) · 2.73 KB
description T-SQL Tutorial: Delete database objects
title T-SQL Tutorial: Delete database objects | Microsoft Docs
ms.custom
ms.date 07/30/2018
ms.prod sql
ms.technology t-sql
ms.reviewer
ms.topic conceptual
helpviewer_keywords
deleting database objects
ms.assetid ecf26dd5-4535-4ed6-86fc-c73f9d9dedec
author MikeRayMSFT
ms.author mikeray
monikerRange >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Lesson 3: Delete database objects

[!INCLUDEtsql-appliesto-ss2008-all-md] This short lesson removes the objects that you created in Lesson 1 and Lesson 2, and then drops the database.

Before you delete objects, make sure you are in the correct database:

USE TestData;  
GO

Revoke stored procedure permissions

Use the REVOKE statement to remove execute permission for Mary on the stored procedure:

REVOKE EXECUTE ON pr_Names FROM Mary;  
GO

Drop permissions

  1. Use the DROP statement to remove permission for Mary to access the TestData database:

    DROP USER Mary;  
GO

  2. Use the DROP statement to remove permission for Mary to access this instance of [!INCLUDEssVersion2005]:

    DROP LOGIN [<computer_name>\Mary];  
GO

  3. Use the DROP statement to remove the store procedure pr_Names:

    DROP PROC pr_Names;  
GO

  4. Use the DROP statement to remove the view vw_Names:

    DROP VIEW vw_Names;  
GO

Delete table

  1. Use the DELETE statement to remove all rows from the Products table:

    DELETE FROM Products;  
GO

  2. Use the DROP statement to remove the Products table:

    DROP TABLE Products;  
GO

Remove database

You cannot remove the TestData database while you are in the database; therefore, first switch context to another database, and then use the DROP statement to remove the TestData database:

USE MASTER;  
GO  
DROP DATABASE TestData;  
GO

This concludes the Writing [!INCLUDEtsql] Statements tutorial. Remember, this tutorial is a brief overview and it does not describe all the options to the statements that are used. Designing and creating an efficient database structure and configuring secure access to the data requires a more complex database than that shown in this tutorial.