GRANT 需求:我想用一个存储过程或者什么语句,将一批表格/存储过程的权限赋予某个角色怎么办比如有下面的表格:
ID 表格名称
1 TableA
2 TableB
3 TableC
.. ..
n TableN
现在我想用一个语句或者存储过程,将这些表格的选择权限赋予一个用户
GRANT SELECT ON [table] TO [rolename] , 这里的表名和角色名好象都不能是变量!!!
ID 表格名称
1 TableA
2 TableB
3 TableC
.. ..
n TableN
现在我想用一个语句或者存储过程,将这些表格的选择权限赋予一个用户
GRANT SELECT ON [table] TO [rolename] , 这里的表名和角色名好象都不能是变量!!!
Exec 'GRANT SELECT ON ' +@table +'TO '+@rolename
DECLARE GrantPermission CURSOR FOR
SELECT tablename
FROM t6OPEN GrantPermissionFETCH NEXT FROM GrantPermission
INTO @tablenameWHILE @@FETCH_STATUS = 0
BEGIN
Exec ('GRANT SELECT ON '+ @tablename+'TO Public')
FETCH NEXT FROM GrantPermission
INTO @tablename
ENDCLOSE GrantPermission
DEALLOCATE GrantPermission
WHILE @@FETCH_STATUS = 0
BEGIN
Exec ('GRANT SELECT ON '+ @tablename+'TO Public')--'To Public'写上角色名称
FETCH NEXT FROM GrantPermission
INTO @tablename
END