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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » SMO/RMO/DMO » Minimum permissions to allow access to...

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

Rate Topic

Display Mode

Topic Options

Author

Message

geoffrey grierson

Posted Wednesday, November 30, 2011 9:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 18, 2012 2:51 AM
Points: 4, Visits: 72

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

SQLRNNR

Posted Wednesday, November 30, 2011 9:45 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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

geoffrey grierson

Posted Thursday, December 01, 2011 2:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 18, 2012 2:51 AM
Points: 4, Visits: 72

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

SQLRNNR

Posted Thursday, December 01, 2011 8:36 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

For your ready made audit, try this:

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

Post #1214686

opc.three

Posted Monday, December 12, 2011 1:57 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 6,728, Visits: 11,774

geoffrey grierson (12/1/2011)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1220469

geoffrey grierson

Posted Tuesday, December 13, 2011 1:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 18, 2012 2:51 AM
Points: 4, Visits: 72

opc.three - Wink

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

opc.three

Posted Tuesday, December 13, 2011 9:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 6,728, Visits: 11,774

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

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1221004

SQLRNNR

Posted Tuesday, December 13, 2011 10:53 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

It sounds very good.

Post #1221048

« Prev Topic | Next Topic »

Permissions