Dump SQL Permissions

Question

Dump SQL Permissions

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Kerry Conner-320974 SSC Enthusiast Points: 109 More actions · Answer 1

This is a great idea for a tool - kudos to the author!

I'm having a problem with it that is probably because of something I'm doing wrong - I thought that I'd throw it out there and see if anyone else had the same issue.

I have an ID that has been granted permission to update a specific column in a table and has no other rights in the table. The DumpSQLSec tool doesn't report any type of access for the ID in the table. It does seem to catch all other permissions for this ID that are granted in regards to entire tables. Is this a bug or is it me?

Thanks!

cmille19 SSCertifiable Points: 5970 More actions · Answer 2

You're right it doesn't handle column permissions, the code is iterating through by object type and then for each object (table,view, stored procedure, function) executing the ListPermissions method. It looks like column is another object in SQL-DMO. I'm currently working on a Powershell script which uses SMO, once its finished I'll write an article about it. The Powershell/SMO script will handle column permissions correctly.

--Chad

John Ranger SSC Journeyman Points: 76 More actions · Answer 3

Hello Steve,

yes I would like to look at it. May I ask you to send it over to me?

With kind regards,

John

cmille19 SSCertifiable Points: 5970 More actions · Answer 4

All,

I've completed a part-time 9 month project to write a utility called "SQL Server PowerShell Extensions (SQLPSX) which uses SMO, PowerShell, and SSRS to produce permissions/security reports including effective permissions by enumerating nested Windows AD/local and SQL Server roles. SQLPSX can be scheduled to run in batch mode and loads the collected security information into a database for reporting and analysis. Check it out on my CodePlex project site.

http://www.codeplex.com/SQLPSX

gene.infelise Newbie Points: 5 More actions · Answer 5

gene.infelise

Newbie

Points: 5

June 15, 2009 at 1:15 pm

#1009867

Where can I download Dumpsqlsec?

cmille19 SSCertifiable Points: 5970 More actions · Answer 6

cmille19

SSCertifiable

Points: 5970

June 15, 2009 at 5:14 pm

#1009965

See links at bottom of article [/url]

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 7

I was just about to roll my own when I came across this looking for an updated version of DumpSec. Thank you, Thank you, Thank You:-D:-D:-D

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

mmahon 28175 Newbie Points: 5 More actions · Answer 8

mmahon 28175

Newbie

Points: 5

February 8, 2011 at 7:44 am

#1283684

what version of client tools does it need?

cmille19 SSCertifiable Points: 5970 More actions · Answer 9

The solution relies on SQL-DMO and Excel. SQL-DMO is deprecated class for managing SQL Server. SQL-DMO is install with SQL Server 2000, but not with SQL Server 2005 or higher. You can install SQL-DMO from by downloading the "Microsoft SQL Server 2005 Backward Compatibility Components" from Microsoft Downloads. This download might be available for SQL Server 2008 also.

bcotton 50758 Newbie Points: 7 More actions · Answer 10

Excelent tool! nice work... any version for SQL Server 2005? Thanks!

cmille19 SSCertifiable Points: 5970 More actions · Answer 11

As mentioned, Dump SQL Permissions relies on deprecated SQL-DMO library. I've release a 2000 and higher tool that uses SMO library here http://www.codeplex.com/SQLPSX. The project is more complex/featured and includes SQL Server Reporting Services Reports.