MS SQL 常用SQL语句（七）：数据库登录名、服务器角色、数据库用户、权限管理等操作sq

七、数据库登录名、服务器角色、权限管理等操作

--1、使用T-SQL修改服务器的认证模式
xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer','LoginMode',N'REG_DWORD',2
--1、windows身份认证
--2、SQL Server身份认证

--2、修改用户名的密码
--语法结构：exec sp_password '旧密码','新密码','登录名';
exec sp_password '123456','sa','sa';

--3、修改登录名的默认数据库(默认是master)
--语法结构：exec sp_defaultdb '登录名','数据库名称';
exec sp_defaultdb 'wjl','test';

--4、修改登录名的默认语言(默认是simplified chinese)
--语法结构：exec sp_defaultdb '登录名','语言名称';
exec sp_defaultlanguage 'wjl','english';

--5、删除登录名
--语法结构：exec sp_droplogin '登录名';
exec sp_droplogin 'wjl'

--6、为数据库添加用户
--语法结构：exec sp_grantdbaccess '登录名','数据库用户名'
use test;
exec sp_grantdbaccess 'wjl','wjlUser';

--7、查看当前数据库中所有用户的信息
use test;
exec sp_helpuser;

--8、查看当前数据库中指定用户的信息
exec sp_helpuser 'wjlUser';

--9、删除当前数据库中指定的用户
exec sp_revokedbaccess 'wjlUser';

--10、为登录名添加服务器角色
--SQLServer中服务器角色和数据库角色权限
--a、bulkadmin：大容量的插入操作
--b、dbcreator：创建和更改数据库
--c、diskadmin：管理磁盘文件
--d、processadmin：管理运行在sql server中的进程
--e、public：提供数据库中用户的管理权限
--f、securityadmin：管理服务器的登录
--g、serveradmin：管理配置服务器范围的设置
--h、setupadmin：库与库管理拓展的存储过程
--i、sysadmin：可以执行sql server安装中的任何操作
--可参考的连接：http://blog.csdn.net/e_online/article/details/4597957
--可参考的连接：http://www.cnblogs.com/xwdreamer/archive/2012/06/25/2561391.html

--使用T-sql进行操作：
--为登录名称wjl添加sysadmin角色
exec sp_addsrvrolemember 'wjl','sysadmin';

--11、为登录名删除指定的服务器角色
--为登录名称wjl删除sysadmin角色
exec sp_dropsrvrolemember 'wjl','sysadmin';

--12、自定义数据库角色
--图形化界面：test数据库--->安全性--->角色--->数据库角色--->右键“添加数据库角色”--->填写“数据库角色名称”和所有者，勾选拥有的架构（选填），添加角色成员--->确定。

--使用T-SQL：
exec sp_addrole 'test_dbAdmin','dbo';
--test_dbAdmin:数据库角色名称 dbo：所有者

--13、删除数据库角色
exec sp_droprole 'test_dbAdmin';

--14、将数据库角色添加给数据库用户
--为数据库用户wjl添加test_dbAdmin数据库角色
exec sp_addrolemember 'test_dbAdmin','wjl';

--15、将数据库用户的某个数据库角色删除
--将数据库用户wjl的test_dbAdmin数据库角色删除
exec sp_droprolemember 'test_dbAdmin','wjl';

--16、权限管理：
--默认管理员登录号：Guest、Public

--给指定的数据库角色添加权限
--授予当前数据库test_dbAdmin角色对于表employee更新的权限，且该角色可以将更新的权限授予其他角色
grant update on employee to test_dbAdmin with grant option 

--授予当前数据库test_dbAdmin角色对于表employee添加数据的权限，但是该角色不能将添加数据的权限授予其他角色
grant insert on employee to test_dbAdmin 

--剥夺某个用户的指定权限，同时剥夺它授予他人权限的权利
revoke update on employee from test_dbAdmin cascade;
--剥夺某个用户的指定权限，不剥夺它授予他人权限的权利
revoke insert on employee from test_dbAdmin

--拒绝被授予某项权限
--拒绝赋予test_dbAdmin对于表employee的更新权限，同时拒绝“授予他人更新权限”的权限
deny update on employee to test_dbAdmin cascade;

--17、使用T-SQL进行数据库备份
--完整备份
backup database test to disk=N'F:\DB_bak\test20161115.bak' with noformat,noinit,name=N'multidatabase-完整数据库备份',skip,norewind,nounload,stats=1; 

--差异备份
backup database test to disk=N'F:\DB_bak\test20161115_different.bak' with differential,noinit,name=N'multidatabase-差异数据库备份',skip,norewind,nounload,stats=10;
--注意：进行差异备份要求备份的数据库至少进行过一次完整备份，否则会报错：
--无法执行数据库 "test2" 的差异备份，因为不存在当前数据库备份。请去掉 WITH DIFFERENTIAL 选项后重新发出 BACKUP DATABASE 以执行数据库的完整备份。 

--事务日期备份(测试未成功，目前不知道原因)
backup log database test to disk=N'F:\DB_bak\test20161115_log2.bak' with noformat,noinit,name=N'multidatabase-事务日志备份',skip,norewind,nounload,stats=10;
--报错：关键字 'database' 附近有语法错误。关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句，那么前一个语句必须以分号结尾。

--18、使用T-SQL进行数据库还原(测试未成功，目前不知道原因)
restore database test from disk=N'F:\DB_bak\test20161115.bak' with file=1,nounload,stats=10;
--报错：尚未备份数据库 "test3" 的日志尾部。如果该日志包含您不希望丢失的工作，请使用 BACKUP LOG WITH NORECOVERY 备份该日志。请使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来只覆盖该日志的内容。