Today's script will return you permissions granted to a user for every online database. The script uses EXECUTE AS so you will need permission to impersonate the user and also uses the fn_my_permissions function so is limited by its functionality. I have used this script to hunt down what I think are security problems caused by permissions being granted to an individual rather than a group.
/*
-----------------------------------------------------------------
User Permissions By Database
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Declare variables
DECLARE
@intMinLoop INT
,@intMaxLoop INT
,@strSQL VARCHAR(1000)
,@strDatabasename VARCHAR(100)
,@strUser VARCHAR(1000)
-- Create table variable
DECLARE @tmpDBtable TABLE
(
AutoID INT IDENTITY (1,1)
,Databasename VARCHAR(500)
)
-- SET the User
SET @strUser = 'domain\user' --or for a sql user SET @strUser = 'user'
-- INSERT the database names
INSERT INTO @tmpDBTable
SELECT NAME FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
-- Setup loop control
SELECT
@intMinLoop = MIN(autoID)
,@intMaxLoop = MAX(autoID)
FROM
@tmpDBTable
-- Get permissions
WHILE @intMinLoop <= @intMaxLoop
BEGIN
SELECT @strDatabasename = Databasename FROM @tmpDBTable WHERE @intMinLoop = AutoID
SELECT @strSQL =
'USE ['+@strDatabasename+'];
EXECUTE AS USER = '''+@strUser+''';
SELECT
'''+@strDatabasename+''' as DatabaseName
,*
FROM fn_my_permissions(NULL,''Database'');
REVERT;'
BEGIN TRY
EXEC (@strSQL)
END TRY
BEGIN CATCH
SELECT @@ERROR, ERROR_MESSAGE()
END CATCH
SET @intMinLoop = @intMinLoop+1
END
Enjoy!
Chris
