--sa用户的操作---
--数据库的创建
create database [sc]
on(name='scdb_data',filename='d:\scdb_data.mdf',size=1,filegrowth=10%)
log on(name='scdb_log',filename='d:\scdb_log.ldf',size=1,filegrowth=10%)
--创建登录
--存储过程
exec sp_addlogin 'dxt','123','scdb'
exec sp_addlogin 'dxt1','123','scdb'
exec sp_addlogin 'dxt2','123','scdb'
exec sp_droplogin 'dxt'—删除登录
 
 
 clip_p_w_picpath002
 
--获取当前数据库的访问权,但还不能做任何操作,如建表,查询,更新等...必须授予相应的权限或角色
exec sp_grantdbaccess 'dxt'
exec sp_revokedbaccess 'dxt'
--权限管理
grant create table to dxt
grant create view to dxt
--授予dxt用户对scdb数据库中student表的select权限
grant select on student to dxt
--授予所有权限
grant all privileges on student to dxt
revoke all privileges on student from dxt
--把对表的查询权限授予给所有用户
grant select on student to public
revoke select on student from public
revoke select on student from dxt
--创建角色
exec sp_addrole select_role--查询角色
exec sp_addrole delete_role--删除角色
exec sp_addrole insert_role--插入角色
exec sp_addrole update_role--更新角色
 
 
clip_p_w_picpath004
 
 
grant select on student to select_role--授予角色查询权限
grant delete on student to delete_role--授予角色删除权限
grant insert on student to insert_role--授予角色插入权限
grant update on student to update_role--授予角色更新权限
如下图(角色insert_role的权限)
 
 
clip_p_w_picpath006
 
 
--将各角色添加至dxt用户,这样dxt用户就拥有了对表student的查询、删除、插入、更新权限...
sp_addrolemember select_role,dxt
sp_addrolemember delete_role,dxt
sp_addrolemember insert_role,dxt
sp_addrolemember update_role,dxt