November 30, 2011 at 9:19 am
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.
November 30, 2011 at 9:45 am
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[/url]
Learn Extended Events
December 1, 2011 at 2:36 am
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]
[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.
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
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)
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
December 1, 2011 at 8:36 am
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[/url]
Learn Extended Events
December 12, 2011 at 1:57 pm
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
December 13, 2011 at 1:19 am
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.
December 13, 2011 at 9:33 am
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
December 13, 2011 at 10:53 am
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[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply