将某用户下所有用户表增删查改权限授予另一个用户
DECLARE ACUR CURSOR FOR
SELECT name FROM SYSOBJECTS WHERE TYPE='U'
DECLARE @NAME VARCHAR(100)
DECLARE @SQL VARCHAR(512)
OPEN ACUR
FETCH NEXT FROM ACUR INTO @NAME
WHILE @@fetch_status=0
BEGIN
EXEC ('grant select,insert,delete,update ON ['+ @NAME+'] TO username;')
FETCH NEXT FROM ACUR INTO @NAME
END
CLOSE ACUR
DEALLOCATE ACUR
如果要将VIEW,FUNCTION,PROCEDURE等数据对象授权给其它用户,只需更改SELECT name FROM SYSOBJECTS WHERE TYPE='U' 中TYPE的类型和grant相应的权限即可.
VIEW:
SELECT name FROM SYSOBJECTS WHERE TYPE='V' and status>0
PROCEDURE:
SELECT name FROM SYSOBJECTS WHERE TYPE='P' and name not like 'dt%'(sysobjects中type='P'的包括系统存储过程,按照几台DB上规律,以dt开头的都是系统存储过程,所以此处我用name not like 'dt%'来剔除系统存储过程,不一定完全准确.)
EXEC ('grant execute ON ['+ @NAME+'] TO username;')
标量函数:
SELECT name FROM SYSOBJECTS WHERE TYPE='FN'
EXEC ('grant execute ON ['+ @NAME+'] TO username;')
表值函数:
SELECT name FROM SYSOBJECTS WHERE TYPE='TF'
EXEC ('grant select ON ['+ @NAME+'] TO username;')
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10640532/viewspace-611061/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10640532/viewspace-611061/