本文主要是介绍SQL SERVER2008 存储过程、表、视图、函数的权限、新建用户权限，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
--创建角色 r_test
EXEC sp_addrole 'r_test'--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'ss_test','123','pubs'--为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'ss_test','u_test'--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'--授予角色 r_test 对 pubs.Orders 表的 SELECT 权限GRANT SELECT ON Orders TO r_testEXEC sp_addrolemember N'db_owner', N'db'----将db 设置为 db_owner 角色中的一员
EXEC sp_droprolemember N'db_owner', N'db'----将db 从 db_owner 角色 去除 ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO dbs ----给db 赋予 拥有 db_datareader 架构 ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO db --- 修改 db_datareader 架构的所有者为db ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [db_datareader] ---修改 db_datareader 角色所拥有的架构 --- 赋予 《授予 XX 权限》
GRANT VIEW DEFINITION ON [dbo].[PSYP_DataBaseBackUp] TO [db] --查看
GRANT ALTER ON [dbo].[PSYP_DataBaseBackUp] TO [db] --修改
GRANT TAKE OWNERSHIP ON [dbo].[PSYP_DataBaseBackUp] TO [db] --接管所有权
GRANT CONTROL ON [dbo].[PSYP_DataBaseBackUp] TO [db] --控制权
GRANT EXECUTE ON dbo.[PSYP_DataBaseBackUp] TO db --执行 ---赋予 《具有授予XX权限》
---如果直接给 具有授予权限 则 默认给予了XX权限
GRANT VIEW DEFINITION ON [dbo].[PSYP_DataBaseBackUp] TO [db] WITH GRANT OPTION --- --查看权限并具有授予权限
GRANT ALTER ON [dbo].[PSYP_DataBaseBackUp] TO [db] WITH GRANT OPTION
GRANT TAKE OWNERSHIP ON [dbo].[PSYP_DataBaseBackUp] TO [db] WITH GRANT OPTION
GRANT CONTROL ON [dbo].[PSYP_DataBaseBackUp] TO [db] WITH GRANT OPTION
GRANT EXECUTE ON [dbo].[PSYP_DataBaseBackUp] TO [db] WITH GRANT OPTION ----拒绝XX权限
DENY VIEW DEFINITION ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
DENY ALTER ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
DENY TAKE OWNERSHIP ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
DENY CONTROL ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
DENY EXECUTE ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE ---收回XX权限
REVOKE VIEW DEFINITION ON [dbo].[PSYP_DataBaseBackUp] TO [db] ---查看定义
REVOKE ALTER ON [dbo].[PSYP_DataBaseBackUp] TO [db] --修改
REVOKE TAKE OWNERSHIP ON [dbo].[PSYP_DataBaseBackUp] TO [db] --接管所有权
REVOKE CONTROL ON [dbo].[PSYP_DataBaseBackUp] TO [db] --控制权
REVOKE EXECUTE ON dbo.[PSYP_DataBaseBackUp] TO db --执行 ---收回XX具有授予权限
REVOKE GRANT OPTION FOR VIEW DEFINITION ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
REVOKE GRANT OPTION FOR ALTER ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
REVOKE GRANT OPTION FOR TAKE OWNERSHIP ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
REVOKE GRANT OPTION FOR CONTROL ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE
REVOKE GRANT OPTION FOR EXECUTE ON [dbo].[PSYP_DataBaseBackUp] TO [db] CASCADE --------给表赋 权限
--- 赋予 《具有授予XX权限》
GRANT INSERT ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT VIEW DEFINITION ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT VIEW CHANGE TRACKING ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT ALTER ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT UPDATE ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT TAKE OWNERSHIP ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT CONTROL ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT DELETE ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT SELECT ON [dbo].[a] TO [db] WITH GRANT OPTION
GRANT REFERENCES ON [dbo].[a] TO [db] WITH GRANT OPTION ----------表 和 视图 具有相同的权限
INSERT --插入
VIEW DEFINITION --查看定义
VIEW CHANGE TRACKING -- 查看更改跟踪
ALTER ---修改
UPDATE ---更新
TAKE OWNERSHIP ---接管所有权
CONTROL ---控制
DELETE ---删除
Select ---选择
REFERENCES --引用 ----存储过程 具有的权限
VIEW DEFINITION ---查看
ALTER ---修改
TAKE OWNERSHIP ---接管所有权
CONTROL ---控制
EXECUTE ---执行 -----标量函数 的权限 VIEW DEFINITION ---查看定义
ALTER ---修改
TAKE OWNERSHIP ---接管所有权
CONTROL ---控制
REFERENCES ---引用
EXECUTE ---执行 ------表值函数 的权限
VIEW DEFINITION ---查看定义
ALTER ---修改
TAKE OWNERSHIP --接管所有权
CONTROL ---控制
REFERENCES ---引用
SELECT ---选择
给cmd用户赋权限：GRANT UPDATE,DELETE,INSERT,SELECT,CREATE PROCEDURE,EXECUTE,REFERENCES TO cmd
cmd用户的权限有：更新、删除、新增、查询、创建存储过程、执行存储过程、引用权限
这篇关于SQL SERVER2008 存储过程、表、视图、函数的权限、新建用户权限的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
