ch03.sql

SQLServer2005基础教程_配套课件和脚本

CREATE LOGIN [MICROSOF-2649F5\ammydolphin] FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks 

CREATE LOGIN Peter WITH PASSWORD = 'drghuds29%dsxmn126932%'

CREATE LOGIN Peterson WITH PASSWORD = 'DRafhr8273%#981kjyrtd256' 
MUST_CHANGE,
CHECK_EXPIRATION = ON

ALTER LOGIN Peterson WITH NAME = Rudolf

ALTER LOGIN Rudolf WITH PASSWORD = 'TOD2983#ddsi%di90125'

ALTER LOGIN Rudolf DISABLE
GO
ALTER LOGIN Rudolf ENABLE
GO

sp_addsrvrolemember 'Rudolf', 'sysadmin'

USE AdventureWorks
CREATE USER Peter_user 
FROM LOGIN Peter

USE AdventureWorks
CREATE USER Bobbie_HRM 
FROM LOGIN [MICROSOF-2649F5\ammydolphin]
WITH DEFAULT_SCHEMA = HRManager

GRANT CONNECT TO guest

DROP USER Bobbie_HRM

ALTER USER Bobbie_HRM
WITH NAME = Tomson_HRM

USE AdventureWorks
GO
CREATE SCHEMA companyGManager

DROP SCHEMA companyGManager

USE AdventureWorks
GO
CREATE SCHEMA companyGManager 
AUTHORIZATION Tomson_HRM

USE AdventureWorks
GO
CREATE SCHEMA companyGManager AUTHORIZATION Tomson_HRM
CREATE TABLE TravelEHuman(EmpID INT, EmpName VARCHAR(20))

DROP TABLE companyGManager.TravelEHuman

USE AdventureWorks 
GO 
CREATE SCHEMA companyGManager AUTHORIZATION Tomson_HRM
CREATE TABLE TravelEHuman(EmpID INT, EmpName VARCHAR(20))
GRANT SELECT TO Peter_user

SELECT *
FROM sys.schemas

CREATE SCHEMA HRManager

USE AdventureWorks 
GO 
ALTER SCHEMA HRManager 
TRANSFER companyGManager.TravelEHuman




USE AdventureWorks 
GO 
DROP SCHEMA HRManager 



USE AdventureWorks 
GO 
CREATE ROLE PrjManager

DROP ROLE PrjManager


USE AdventureWorks 
GO 
CREATE ROLE PrjManager
AUTHORIZATION Peter_user


USE AdventureWorks 
GO 
sp_addrolemember 'PrjManager', 'Tomson_HRM'


USE AdventureWorks 
GO 
sp_addrolemember 'PrjManager', 'PrjManager'


USE AdventureWorks 
GO 
CREATE APPLICATION ROLE alter_HR_salary
WITH PASSWORD = 'ABCabc123#!dsr',
DEFAULT_SCHEMA = companyGManager

USE AdventureWorks 
GO 
sp_setapprole 'alter_HR_salary', 'ABCabc123#!dsr'

sp_unsetapprole alter_HR_salary 'ABCabc123#!dsr'

USE AdventureWorks  
GO  
ALTER APPLICATION ROLE alter_HR_salary 
WITH NAME = new_alter_HR_salary, 
PASSWORD = 'newPASSWORD123!djf', 
DEFAULT_SCHEMA = dbo 

USE AdventureWorks
GRANT CONTROL TO Tomson_HRM
GO
GRANT CREATE TABLE TO Peter_user
GO
GRANT CREATE TABLE TO Henry WITH GRANT OPTION
GO



USE AdventureWorks
GRANT SELECT ON Sales.Customer TO Henry
GO
GRANT SELECT ON OBJECT::Sales.CustomerAddress TO Henry WITH GRANT OPTION
GO
GRANT SELECT ON Sales.Store(CustomerID, SalesPersonID) TO Henry 
GO



USE AdventureWorks
REVOKE SELECT ON Sales.Customer FROM Henry
GO



USE AdventureWorks
REVOKE SELECT ON OBJECT::Sales.CustomerAddress FROM Henry CASCADE
GO

USE AdventureWorks
DENY SELECT ON Sales.Store(CustomerID, SalesPersonID) TO Henry
GO

DROP CERTIFICATE FOgisuCert09

USE MASTER;
CREATE CERTIFICATE FOgisuCert09
    WITH SUBJECT = 'FOgisu certificate in master database',
	START_DATE = '02/02/2006',
    EXPIRY_DATE = '02/02/2009';
GO
CREATE LOGIN FOgisu FROM CERTIFICATE FOgisuCert09;
DROP LOGIN FOgisu
GO

USE MASTER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ABC123abc%#!';
GO