第10章 代码 实验10-2 代码Mary的创建授权.sql

第10章实验2
USE [master]
GO
CREATE DATABASE [ adageOfEncouragement] ON PRIMARY
( NAME = N' adageOfEncouragement', FILENAME = N'E:\MSSQLexperiment\ adageOfEncouragement.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N' adageOfEncouragement_log', FILENAME = N'E:\MSSQLexperiment\ adageOfEncouragement_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE [ adageOfEncouragement]
GO

CREATE TABLE [dbo].[ adage](
[id] [int] IDENTITY(1,1) NOT NULL,
[ adage] [nvarchar](max) NULL,
[author] [nchar](20) NULL
) ON [PRIMARY]
GO


INSERT INTO [ adageOfEncouragement].[dbo].[ adage]([ adage],[author])
VALUES('好好学习,天天向上!','毛泽东')
INSERT INTO [ adageOfEncouragement].[dbo].[ adage]([ adage],[author])
VALUES('志不强者智不达.','墨翟')
INSERT INTO [ adageOfEncouragement].[dbo].[ adage]([ adage],[author])
VALUES('One today is worth two tomorrow.',' ')
GO


CREATE LOGIN [T-35E047250FA24\Mary]
FROM WINDOWS
WITH DEFAULT_DATABASE = [adageOfEncouragement];
GO
USE [AdageOfEncouragement];
GO

--在数据库中创建用户
CREATE USER [Mary] FOR LOGIN [T-35E047250FA24\Mary];
GO

CREATE VIEW vw_adage
AS
SELECT [id],[adage],[author]
FROM [adageOfEncouragement].[dbo].[adage] ;
GO
SELECT * FROM vw_adage;
GO


CREATE PROCEDURE pr_author @VarAuthor nchar(20)
AS
BEGIN
-- The print statement returns text to the user
PRINT '查询出自 ' + CAST(@VarAuthor AS varchar(20))+'的励志格言';
-- A second statement starts here
SELECT [id],[adage],[author] FROM vw_adage
WHERE author=@VarAuthor;
END
GO
EXECUTE pr_author '墨翟';
GO
GRANT EXECUTE ON pr_author TO Mary;
GO

--删除权限和对象
--1.在删除对象之前，请确保使用正确的数据库：
USE adageOfEncouragement;
GO
--2.使用 REVOKE 语句删除 Mary 对存储过程的执行权限：
REVOKE EXECUTE ON pr_author FROM Mary;
GO
--3.使用 DROP 语句删除 Mary 对 adageOfEncouragement 数据库的访问权限：
DROP USER Mary;
GO
--4.使用 DROP 语句删除 Mary 对此 SQL Server 2008 实例的访问权限。
DROP LOGIN [T-35E047250FA24\Mary];
GO
--5.使用 DROP 语句删除存储过程 pr_author：
DROP PROC pr_author;
GO
--6.使用 DROP 语句删除视图 vw_adage：
DROP View vw_adage;
GO
--7.使用 DELETE 语句删除 adage 表中的所有行：
DELETE FROM adage;
GO
--8.使用 DROP 语句删除 adage 表：
DROP Table adage;
GO

--9.正使用adageOfEncouragement 数据库时，无法删除该数据库；因此，请首先将上下文切换到其他数据库，再使用 DROP 语句删除adageOfEncouragement 数据库：
USE MASTER;
GO
DROP DATABASE adageOfEncouragement;
GO