目前無法馬上提出以使用者為主來查詢資料庫權限的方式。
暫時先提出替代解決方案，以資料庫為主來查詢使用者權限，實際語法紀錄於下：
SET NOCOUNT ON
DECLARE @DBName varchar(50)
SET @DBName = ''
DECLARE @CommandString varchar(8000)
SET @CommandString =''
DECLARE mycursor CURSOR FOR
SELECT name FROM master.sys.databases
OPEN mycursor
FETCH NEXT FROM
mycursor
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CommandString = @CommandString + 'USE ' + @DBName + ' select dbpri.name,
dbper.permission_name ';
SET @CommandString = @CommandString + 'from sys.database_permissions dbper ';
SET @CommandString = @CommandString + 'left JOIN sys.database_principals dbpri ';
SET @CommandString = @CommandString + 'on dbpri.principal_id = dbper.grantee_principal_id ';
SET @CommandString = @CommandString + 'WHERE (dbper.class = 0);';
SET @CommandString = @CommandString + CHAR(10);
select @CommandString;
SET @CommandString = '';
FETCH NEXT FROM
mycursor INTO @DBName
END
CLOSE mycursor
DEALLOCATE mycursor
GO
該語法會跑出每個資料庫的select語法，
將其一個一個執行後，可以得到資料庫中的使用者權限資訊。
|
參考資料：
https://social.msdn.microsoft.com/Forums/zh-TW/dd108587-90b9-4820-a531-5d27c26919ee/sql2005sql-server-db-?forum=240
沒有留言:
張貼留言