将某用户下所有用户表增删查改权限授予另一个用户
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 ('grantexecuteON ['+ @NAME+'] TO username;')
标量函数:
SELECT name FROM SYSOBJECTS WHERE TYPE='FN'
EXEC ('grantexecuteON ['+ @NAME+'] TO username;')
表值函数:
SELECT name FROM SYSOBJECTS WHERE TYPE='TF'
EXEC ('grant selectON ['+ @NAME+'] TO username;')
SQLSERVER批量授权
最新推荐文章于 2022-04-17 15:39:59 发布