1、查询用户具有的权限
exec sp_helprotect @username = 'USER1';
2、切换用户
EXECUTE AS USER = 'USER1';
REVERT; -- 切换到执行最后一次execute as user前的用户
3、创建用户
CREATE USER USER1 WITHOUT LOGIN;
--也可以通过login的用户来实现。
4、授权与收回
GRANT INSERT, UPDATE(SCORE)
ON STUDENTS
TO USER1;
REVOKE INSERT
ON STUDENTS
TO USER1;
5、查看当前user
SELECT USER;
6、创建登录用户和数据库用户
create login LIYONG with password='123456'
create user LIYONG for login LIYONG
7、撤销登录用户
exec sp_revokedbaccess 'LIYONG';
exec sp_droplogin 'LIYONG';
8、查看所有用户
SELECT Name FROM Master..SysDatabases ORDER BY Name
9、创建角色并为角色添加成员
EXEC sp_addrole 'p_test'
EXEC sp_addrolemember 'p_test','m_test'