SQL 数据库权限设计

--创建登录账号
use master
go
exec sp_grantlogin 'hyc\TeacherWang'
exec sp_addlogin 'xiaoliu','1234'

--创建数据库用户
use StudentManageDB
go
exec sp_grantdbaccess 'hyc\TeacherWang','wangUser01'
exec sp_grantdbaccess 'xiaoliu','xiaoliuUser02'

use SaleManagerDB
go
exec sp_grantdbaccess 'xiaoliu','xiaoliuUser03'

--删除登录帐号
use master
go
exec sp_droplogin 'xiaoliu'
--删除数据库用户
use StudentManageDB
go
exec sp_dropuser 'xiaoliuUser02'
use SaleManagerDB
go
exec sp_dropuser 'xiaoliuUser03'

--给数据库用户授权
use StudentManageDB
go
--分配权限
grant select,insert,update on Students to xiaoliuUser02
grant create table to xiaoliuUser02
--收回权限
revoke select,insert,update on Students to xiaoliuUser02

use StudentManageDB
go
--给数据库用户赋予角色
exec sp_addrolemember 'db_owner','xiaoliuUser02'
--删除数据库用户角色
exec sp_droprolemember 'db_owner','xiaoliuUser02'