SQLserver数据库权限授权+回收

数据库权限授权+回收

1.查询账户所有权限

exec sp_helprotect @username = '用户名'

2.授权账户

SELECT 
'GRANT INSERT ON [dbo].['+NAME+'] TO [账户名]' 插入,
'GRANT VIEW DEFINITION ON [dbo].['+NAME+'] TO [账户名]' 查看定义,
'GRANT VIEW CHANGE TRACKING ON [dbo].['+NAME+'] TO [账户名]' 查看更改跟踪,
'GRANT ALTER ON [dbo].['+NAME+'] TO [账户名]' 更改,
'GRANT UPDATE ON [dbo].['+NAME+'] TO [账户名]' 更新,
'GRANT TAKE OWNERSHIP ON [dbo].['+NAME+'] TO [账户名]' 接管所有权,
'GRANT CONTROL ON [dbo].['+NAME+'] TO [账户名]' 控制,
'GRANT DELETE ON [dbo].['+NAME+'] TO [账户名]' 删除,
'GRANT SELECT ON [dbo].['+NAME+'] TO [账户名]' 选择,
'GRANT REFERENCES ON [dbo].['+NAME+'] TO [账户名]' 引用
FROM SYSOBJECTS WHERE NAME LIKE '表名'

3.权限回收

REVOKE update ON 表名 FROM 用户名;

4.授权创建视图

grant create view to 用户名