Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Minimum permissions to allow access to SMO.Server.Databases.Users list Expand / Collapse
Author
Message
Posted Wednesday, November 30, 2011 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 2:51 AM
Points: 4, Visits: 73
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.
Post #1214041
Posted Wednesday, November 30, 2011 9:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,631, Visits: 15,289
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1214072
Posted Thursday, December 1, 2011 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 2:51 AM
Points: 4, Visits: 73
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] [guest] [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.
[color=red]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[/color]

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)
[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, Jason
Post #1214430
Posted Thursday, December 1, 2011 8:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,631, Visits: 15,289
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1214686
Posted Monday, December 12, 2011 1:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 7,097, Visits: 12,600
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
Post #1220469
Posted Tuesday, December 13, 2011 1:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 2:51 AM
Points: 4, Visits: 73
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.
Post #1220650
Posted Tuesday, December 13, 2011 9:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 7,097, Visits: 12,600
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
Post #1221004
Posted Tuesday, December 13, 2011 10:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,631, Visits: 15,289
It sounds very good.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1221048
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse