请问如何对大量表进行授权操作?这些表可以用select语句容易筛选的,几百个表。主要是如何对大量对象(包括表,view等)进行授权?GRANT SELECT ON xxx TO [Assistant]
xxx不能是子查询,否则就容易了,select name from sysobjects where name like '__yyy___'
有什么办法吗?我这里xxx其实就是ww001-ww250之类,可是量很多,有几种类型。
xxx不能是子查询,否则就容易了,select name from sysobjects where name like '__yyy___'
有什么办法吗?我这里xxx其实就是ww001-ww250之类,可是量很多,有几种类型。
for
select name from sysobjects where name like '__yyy___'
declare @name sysname
open tb
fetch tb into @name
while @@fetch_status = 0
begin
EXEC(N'GRANT SELECT ON ' + @name + ' TO [Assistant]'
fetch tb into @name
end
close tb
deallocate tb
from sysobjects where name like '__yyy___'
然后复制查询结果到查询分析器中 按 F5 执行一下就行了嘛
Incorrect syntax near the keyword 'fetch'.
EXEC(N'GRANT SELECT ON ' + @name + ' TO [Assistant]'
这句在出错把它注释掉就正常了(不过运行它对我意义也没了)
如果我注释了exec,在下面加一句print @name能显示所有表。请再给看看,多谢!
for
select name from sysobjects where name like '__yyy___'
declare @name sysname
open tb
fetch tb into @name
while @@fetch_status = 0
begin
EXEC(N'GRANT SELECT ON ' + @name + ' TO [Assistant]') -- 括号不匹配而已
fetch tb into @name
end
close tb
deallocate tb