sql sever权限了解用代码（login，user，role）

原文在这里http://www.cnblogs.com/ysw-go/p/4971993.html

第一步建立数据库library

第二步建立基本表：学生表student、图书表book、借阅表borrow，并各导入一定的数据。

第三步建立不同权限的用户(也可以拿鼠标点，不过敲一遍就懂了):

-----------------------------------------

USE library
GO
CREATE LOGIN stu WITH PASSWORD = '123';


USE library;
GO
CREATE USER student_user FOR LOGIN stu;
------------------------------------------
USE library;
GO
CREATE LOGIN admin WITH PASSWORD = '123';


USE library;
GO
CREATE USER admin_user FOR LOGIN admin;
--------------------------------------------
CREATE ROLE student_role;
GRANT SELECT ON book TO student_role;
GRANT SELECT ON borrow TO student_role;
GRANT SELECT ON student to student_role;
--------------------------------------------
CREATE ROLE admin_role;
GRANT SELECT,UPDATE,DELETE,INSERT ON book TO admin_role;
GRANT SELECT,UPDATE,DELETE,INSERT ON borrow TO admin_role;
GRANT SELECT,UPDATE,DELETE,INSERT ON student TO admin_role;
---------------------------------------------


EXEC sp_addrolemember 
@rolename = 'student_role',
@membername = 'student_user';


EXEC sp_addrolemember
@rolename = 'admin_role',
@membername = 'admin_user';

/*
use library;
go
exec sp_helprotect @username = 'admin_user';

use library;

go
exec sp_helprotect @username = 'student_user';
*/