SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
geoffrey grierson
geoffrey grierson
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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: (Smile [], 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.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32328 Visits: 18552
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

geoffrey grierson
geoffrey grierson
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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: (Smile [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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32328 Visits: 18552
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14949 Visits: 14396
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
geoffrey grierson
geoffrey grierson
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14949 Visits: 14396
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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32328 Visits: 18552
It sounds very good.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search