/*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
转载于:https://www.cnblogs.com/HeroBeast/archive/2011/06/14/2080775.html