ch04 security.sql

SQL+Server实用简明教程电子教案和实例源文件

CREATE LOGIN [ABCSERVER\Bobbie] FROM WINDOWS

 

CREATE LOGIN [ABCSERVER\Bobbie] FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks 



CREATE LOGIN Peter WITH PASSWORD = 'adudrttghu96%dsxmn2531562%'






CREATE LOGIN Peterson WITH PASSWORD = 'ABKldhr546653%#765rkjy3jddg3rtd23' 
MUST_CHANGE,
CHECK_EXPIRATION = ON






ALTER LOGIN Peterson WITH NAME = Rudolf







ALTER LOGIN Rudolf WITH PASSWORD = 'XYkdasjl47687#dgdhgg%di32781'





-- 禁用Rudolf登录名
ALTER LOGIN Rudolf DISABLE
GO
-- 启用Rudolf登录名
ALTER LOGIN Rudolf ENABLE
GO



sp_addsrvrolemember 'Rudolf', 'sysadmin'




USE AdventureWorks
CREATE USER Peter_user 
FROM LOGIN Peter



USE AdventureWorks
CREATE USER Bobbie 
FROM LOGIN [ABCSERVER\Bobbie]
WITH DEFAULT_SCHEMA = HRManager

USE AdventureWorks
GRANT CONNECT TO guest





USE AdventureWorks
ALTER USER Bobbie
WITH NAME = Tomson



USE AdventureWorks
GO
CREATE SCHEMA companyGManager


DROP SCHEMA companyGManager


USE AdventureWorks
GO
CREATE SCHEMA SaleManager 
AUTHORIZATION Tomson


USE AdventureWorks
GO
CREATE SCHEMA Manager AUTHORIZATION Tomson
CREATE TABLE ElecTravelHuman(
EmpID INT, 
EmpName VARCHAR(20))



DROP TABLE Manager.ElecTravelHuman
DROP SCHEMA Manager


USE AdventureWorks 
GO 
CREATE SCHEMA Manager AUTHORIZATION Tomson
CREATE TABLE ElecTravelHuman(
EmpID INT, 
EmpName VARCHAR(20))
GRANT SELECT TO Peter_user



SELECT *
FROM sys.schemas



USE AdventureWorks 
GO 
ALTER SCHEMA SaleManager 
TRANSFER Manager.ElecTravelHuman







USE AdventureWorks 
GO 
DROP SCHEMA SaleManager 





USE AdventureWorks 
GO 
CREATE ROLE ProjectManager


DROP ROLE ProjectManager



USE AdventureWorks 
GO 
CREATE ROLE ProjectManager
AUTHORIZATION Peter_user





USE AdventureWorks 
GO 
sp_addrolemember 'ProjectManager', 'Tomson'




USE AdventureWorks 
GO 
sp_addrolemember 'PrjManager', 'PrjManager'



USE AdventureWorks 
GO 
CREATE APPLICATION ROLE alter_HR_salary
WITH PASSWORD = 'strRbc1673#!asdfklj',
DEFAULT_SCHEMA = Manager



USE AdventureWorks 
GO 
sp_setapprole 'alter_HR_salary', 'strRbc1673#!asdfklj'



sp_unsetapprole alter_HR_salary 'strRbc1673#!asdfklj'




USE AdventureWorks  
GO  
ALTER APPLICATION ROLE alter_HR_salary 
WITH NAME = new_alter_HR_salary, 
PASSWORD = 'newPASSWORDstrRbc1673#!asdfklj', 
DEFAULT_SCHEMA = dbo 








USE AdventureWorks
GRANT CONTROL TO Tomson
GO
GRANT CREATE TABLE TO Peter_user
GO
GRANT CREATE TABLE TO Cleon WITH GRANT OPTION
GO







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




USE AdventureWorks
REVOKE SELECT ON Sales.Customer FROM Cleon
GO




USE AdventureWorks
DENY SELECT ON Sales.Store(CustomerID, SalesPersonID) TO Cleon
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