geoffrey grierson (12/1/2011)
Good morning JasonI 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