A Script A Day - Day 11 - User Permissions By Database

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.co.uk

      -----------------------------------------------------------------

      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