Monday 20 February 2012

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

No comments:

Post a Comment