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_test

EXEC 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用户的权限有：更新、删除、新增、查询、创建存储过程、执行存储过程、引用权限