InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 15:46:43 GMT20http://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspxIt sounds very good.Tue, 13 Dec 2011 10:53:06 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspxPath of least resistance...gotcha. Happy you got a comfortable way forward :-)Tue, 13 Dec 2011 09:33:48 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspxopc.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.Tue, 13 Dec 2011 01:19:36 GMTgeoffrey griersonhttp://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspx[quote][b]geoffrey grierson (12/1/2011)[/b][hr]Good morning JasonI did:USE masterGOGRANT 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[code="other"]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[/code][/quote]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.Mon, 12 Dec 2011 13:57:12 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspxFor your ready made audit, try this:[url]http://jasonbrimhall.info/2010/03/19/security-audit/[/url]Thu, 01 Dec 2011 08:36:33 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspxGood morning JasonI did:USE masterGOGRANT 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[code="other"]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[/code]When I run it with sysadmin, it printsdatabase1[dbo] [guest] [INFORMATION_SCHEMA] [sys]When I run it with public (or also with VIEW SERVER STATE granted) it printsdatabase1An exception occurred while executing a Transact-SQL statement or batch.[color=red]ForEach-Object : The following exception was thrown when trying to enumerate the collection: "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[/color]The SS profiler for the working case shows[code="sql"]use [database1]SELECT u.name AS [Name] FROM sys.database_principals AS uWHERE (u.type in ('U', 'S', 'G', 'C', 'K'))ORDER BY [Name] ASC[/code]For the failing case, there is an error message before the 'use database1' batch starts (can't understand why it's before)[code="sql"]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, JasonThu, 01 Dec 2011 02:36:39 GMTgeoffrey griersonhttp://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspxTry View Server State (this will also give that user access to view several other catalog views too).Wed, 30 Nov 2011 09:45:58 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1214041-22-1.aspxI have a Powershell script which I would like to run using less than sysadmin permissions.It creates a New-Object Microsoft.SqlServer.Management.Smo.ServerThen uses FOREACH-OBJECT in the Databases collectionThen uses FOREACH-OBJECT in the Users collection, grabs the name and the loginThis is ok for a sysadmin account, but for an ordinary user I get an exceptionThe 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 : ExceptionInGetEnumeratorPublic 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.Wed, 30 Nov 2011 09:19:21 GMTgeoffrey grierson