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