数据库用户操作相关

/*create a SQL Server authenticated login called LoginName，CHECK_POLICY=ON默认就是on可以不需要，但CHECK_EXPIRATIONl默认是off*/
CREATE LOGIN LoginName WITH PASSWORD = ‘StRonGPassWord1’, CHECK_POLICY=ON,
CHECK_EXPIRATION=ON;

/*To grant a Windows account access to your SQL Server*/
CREATE LOGIN [Domain\AccountName] FROM WINDOWS;

/*add the login ‘‘MyLogin’’ to the sysadmin role*/
EXEC sp_addsrvrolemember ‘MyLogin’, ‘sysadmin’;

/*LOGINPROPERTY检查账户的各属性*/
SELECT LOGINPROPERTY(‘MyLogin’, ‘IsMustChange’);

/*查看登录账户的server的id和sid*/
SELECT name, principal_id, sid FROM sys.server_principals;

/*查看数据库用户的id和sid*/
SELECT name, principal_id, sid FROM sys.database_principals;


/*赋与用户使用profiler的权限*/
GRANT ALTER TRACE TO [LoginName]

/*创建credential(凭据)*/
CREATE CREDENTIAL [WindowsAdmin]
WITH IDENTITY = N‘MyDomain\Administrator’,
SECRET = N‘password’


/*创建角色*/
CREATE ROLE [WebUsers] AUTHORIZATION [SalesManager]


/*创建用户*/
CREATE USER [LoginName] FOR LOGIN [LoginName]

/*给一个角色添加用户,N表示unicode字符*/
EXEC sp_addrolemember N‘db_datawriter’, N‘LoginName’

/*赋与用户或角色权限*/
GRANT CREATE TABLE TO [LoginName]
GRANT EXECUTE ON SprocName to [LoginName]
/*在某一schema上的执行权限*/
GRANT ALTER ON SCHEMA::[dbo] TO [SchemaExampleLogin]
GO

/*创建schema*/
CREATE SCHEMA [TestSchema] AUTHORIZATION [SchemaExampleLogin]

/*改变对像的schema*/
CREATE SCHEMA SecondSchema
GO
ALTER SCHEMA SecondSchema TRANSFER TestSchema.TestTable
GO
ALTER SCHEMA SecondSchema TRANSFER TestSchema.TableDesignerTest
GO


/*Change the schema owner*/
ALTER AUTHORIZATION ON SCHEMA::TestSchema TO dbo
GO


/*授与对某用户的模拟权限，一般不要模拟权限太大的用户，如这个dbo*/
GRANT IMPERSONATE ON USER::dbo TO SchemaExampleLogin;

/*execute as 示例，此存储过程运行结束，对用户的模拟也将结束*/
ALTER PROC DynamicSQLExample
@OrderBy Varchar(20)
AS
EXECUTE AS USER = ‘dbo’
DECLARE @strSQL varchar(255)
SET @strSQL = ‘SELECT * FROM dbo.TestTableProblem ’
SET @strSQL = @strSQL + ‘ORDER BY ’ + @OrderBy
EXEC (@strSQL)
SELECT USER_NAME() as LoginNm,
USER_NAME() as UserNm,
ORIGINAL_LOGIN() as OriginalLoginNm;
GO


/*login context switch 模拟一个登录,使用完这个权限后，要使用revert将权限返回*/
EXECUTE AS login = ‘SomeLogin’


/*fn_my_permissions函数查询权限示例*/
SELECT * FROM fn_my_permissions(‘TestSchema’, ‘SCHEMA’)
SELECT * FROM fn_my_permissions(‘dbo.TestTableProblem’, ‘OBJECT’)

EXECUTE AS Login = ‘SomeLogin’
SELECT *
FROM fn_my_permissions(NULL, ‘SERVER’)
REVERT
/*user也可以换成login看是否可以模拟login*/
SELECT * FROM fn_my_permissions(‘SchemaExamplelogin’, ‘USER’);


/*一个给数据加密的例子*/
use testdb
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456'
go

CREATE CERTIFICATE [CertTest]
WITH SUBJECT = 'User defined subject. This key will protect the secret data.'
go

CREATE SYMMETRIC KEY [SymKeyTest]
WITH ALGORITHM = TRIPLE_DES --AES_128 Fine too
ENCRYPTION BY CERTIFICATE [CertTest]
go
OPEN SYMMETRIC KEY [SymKeyTest]
DECRYPTION BY CERTIFICATE [CertTest]

DECLARE @Key_Guid AS UNIQUEIDENTIFIER
SET @Key_Guid = key_guid( 'SymKeyTest')

insert into t1(name) values(ENCRYPTBYKEY(@key_guid,N'encrypt test1'))
insert into t1(name) values(ENCRYPTBYKEY(@key_guid,N'encrypt test2'))

select * from t1

select convert(nvarchar(200),DECRYPTBYKEY(name)) from t1
go

CLOSE SYMMETRIC KEY SymKeyTest


/*拒绝对某列查询*/
DENY SELECT (CardNumber) on dbo.CustomerCreditCards to LowPrivLogin

/*开启数据库的tde*/
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘2008TDEexample’
CREATE CERTIFICATE CertForAdventureWorks2008
WITH SUBJECT = ‘Certificate for AdventureWorks2008 TDE’
GO
BACKUP CERTIFICATE CertForAdventureWorks2008
TO FILE = ‘CertForAdventureWorks2008.cer’
WITH PRIVATE KEY ( FILE = ‘CertForAdventureWorks2008.key’ ,
ENCRYPTION BY PASSWORD = ‘2008TDEexample’ )
GO
USE AdventureWorks2008
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE CertForAdventureWorks2008
GO
ALTER DATABASE AdventureWorks2008
SET ENCRYPTION ON
GO


/*开启sqlserver的ekm providers*/
sp_configure ‘show advanced’, 1
GO
RECONFIGURE
GO
sp_configure ‘EKM provider enabled’, 1
GO
RECONFIGURE
GO

/*创建审核对像audit*/
use master;
CREATE SERVER AUDIT [Audit-EmployeeQueries]
TO FILE
(FILEPATH = N‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\’);

/*创建审核规范*/
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec-FailedLogins]
FOR SERVER AUDIT [Audit-FailedLogins]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

/*创建针对数据库表的select审核规范*/
USE [AdventureWorks2008]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec-EmployeesTable]
FOR SERVER AUDIT [Audit-EmployeeQueries]
ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [public])
WITH (STATE = ON)
GO