Minimum permissions to allow access to SMO.Server.Databases.Users list

  • I have a Powershell script which I would like to run using less than sysadmin permissions.

    It creates a New-Object Microsoft.SqlServer.Management.Smo.Server

    Then uses FOREACH-OBJECT in the Databases collection

    Then uses FOREACH-OBJECT in the Users collection, grabs the name and the login

    This is ok for a sysadmin account, but for an ordinary user I get an exception

    The following exception was thrown when trying to enumerate the collection: "An exception

    occurred while executing a Transact-SQL statement or batch.".

    At line:1 char:4

    + $_. <<<< Users

    + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException

    + FullyQualifiedErrorId : ExceptionInGetEnumerator

    Public server role gives access to the list of databases, is there a server setting which will give me access to the users in a database?

    Sorry if the question is a bit garbled; it's tired and I'm getting late. Thanks for your help.

  • Try View Server State (this will also give that user access to view several other catalog views too).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Good morning Jason

    I did:

    USE master

    GO

    GRANT VIEW SERVER STATE TO [dom\acc]

    but the result is the same.

    Today I'll make a fresh start on the problem. No more googlebing for a quick fix.

    Here is my test script

    function main {

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "YOUR SQLSERVER NAME"

    # for each database in the instance

    $serverInstance.Databases | FOREACH-OBJECT {

    trap {

    Write-Host($_.Exception.Message)

    break

    }

    Write-Host($_.Name)

    Write-Host($_.Users)

    break

    }

    }

    main

    When I run it with sysadmin, it prints

    database1

    [dbo]

    [INFORMATION_SCHEMA] [sys]

    When I run it with public (or also with VIEW SERVER STATE granted) it prints

    database1

    An exception occurred while executing a Transact-SQL statement or batch.

    ForEach-Object : The following exception was thrown when trying to enumerate the collectio

    n: "An exception occurred while executing a Transact-SQL statement or batch.".

    At M:\Script\test.ps1:6 char:44

    + $serverInstance.Databases | FOREACH-OBJECT <<<< {

    + CategoryInfo : NotSpecified: (:) [ForEach-Object], ExtendedTypeSystemExcep

    tion

    + FullyQualifiedErrorId : ExceptionInGetEnumerator,Microsoft.PowerShell.Commands.ForE

    achObjectCommand

    The SS profiler for the working case shows

    use [database1]

    SELECT u.name AS [Name]

    FROM sys.database_principals AS u

    WHERE (u.type in ('U', 'S', 'G', 'C', 'K'))

    ORDER BY [Name] ASC

    For the failing case, there is an error message before the 'use database1' batch starts (can't understand why it's before)

    The server principal "dom\acc" is not able to access the database "database1" under the current security context.

    [\code]

    So that's clear enough. I'm going to abandon this project and attack it from another direction.

    I want to gather all the logins on a sql server in a table with a column where I can add a note about the creation of the login and columns showing the access rights of the login. Our security team can then look through a report of this table and feel contentment or righteous indignation depending on how leaky the configuration is.

    Now, back to googlebing for a ready-made account audit.

    Thanks for trying, Jason

  • For your ready made audit, try this:

    http://jasonbrimhall.info/2010/03/19/security-audit/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • geoffrey grierson (12/1/2011)


    Good morning Jason

    I did:

    USE master

    GO

    GRANT VIEW SERVER STATE TO [dom\acc]

    but the result is the same.

    Today I'll make a fresh start on the problem. No more googlebing for a quick fix.

    Here is my test script

    function main {

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "YOUR SQLSERVER NAME"

    # for each database in the instance

    $serverInstance.Databases | FOREACH-OBJECT {

    trap {

    Write-Host($_.Exception.Message)

    break

    }

    Write-Host($_.Name)

    Write-Host($_.Users)

    break

    }

    }

    main

    I spent a little time trying this on SQL 2005 and could only get things going after issuing a GRANT CONTROL SERVER to the login. However, that is effectively the same as adding the login to the sysadmin role. I could not find a lesser server-level permission that would allow the login to do what you are wanting.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three - 😉

    I'm going in the direction Jason suggested; the data his script gathers is better aligned with my needs (as well as being feasible).

    Which rule didn't I follow? If it seems difficult, you're probably doing the wrong thing.

  • Path of least resistance...gotcha. Happy you got a comfortable way forward 🙂

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • It sounds very good.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply