Object Level Privileges to File

Question

Object Level Privileges to File

jayoub

SSCertifiable

Points: 6991
More actions
October 25, 2017 at 5:56 pm

#402619

I am new at SSIS and want to execute the "Object level privilege" query found on the sqlservercentral.com site to a file using SSIS. My ultimate goal is to use SSIS to get the Object level privileges of each database to a file before I refresh a DEV environment from PRD
I first simply need to execute the query on one database and have it generate a file with all the object level privileges.
I have tried the OLE DB source, the execute SQL and the flat file destination tasks with no luck. It seams that if the query does not have columns then SSIS cannot deal with it and as we know the object level privilege output is like text on a notepad page.
If you can just point me in the the correct direction it would be a great help.
I am wondering if executing a powershell command in a Execute Process Task would be a better Option. If SSIS does not work I might move to doing the entire thing in Powershell.
Any help is appreciated.
Jeff

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246956 More actions · Answer 1

jayoub - Wednesday, October 25, 2017 5:56 PM
I am new at SSIS and want to execute the "Object level privilege" query found on the sqlservercentral.com site to a file using SSIS. My ultimate goal is to use SSIS to get the Object level privileges of each database to a file before I refresh a DEV environment from PRD
I first simply need to execute the query on one database and have it generate a file with all the object level privileges.
I have tried the OLE DB source, the execute SQL and the flat file destination tasks with no luck. It seams that if the query does not have columns then SSIS cannot deal with it and as we know the object level privilege output is like text on a notepad page.
If you can just point me in the the correct direction it would be a great help.
I am wondering if executing a powershell command in a Execute Process Task would be a better Option. If SSIS does not work I might move to doing the entire thing in Powershell.
Any help is appreciated.

Please remind us: which query is it that you are referring to?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jayoub SSCertifiable Points: 6991 More actions · Answer 2

Thanks for the reply. The script is located here

http://www.sqlservercentral.com/scripts/Security/71562/

Jeff

Phil Parkin SSC Guru Points: 246956 More actions · Answer 3

jayoub - Wednesday, October 25, 2017 6:23 PM
Thanks for the reply. The script is located here
http://www.sqlservercentral.com/scripts/Security/71562/

OK, took a look at that.
I suggest that you modify the script such that a temp table is created, something like this

CREATE TABLE #Privs(PrivsId int identity(1,1) PRIMARY KEY CLUSTERED, DetailText VARCHAR(2048))

And then you replace the print statement with
INSERT #Privs(DetailText) SELECT @sql
And then at the end of the script you add
SELECT DetailText FROM #Privs ORDER BY PrivsId

Only then will you be in a position to use this as an OLEDB source.
Because you are returning results from a temp table, you may have to create the above as a stored procedure and then use EXECUTE [procname] WITH RESULT SETS in your OLEDB source. I appreciate that this is quite a lot to take in for an SSIS newbie. Good luck and come back with any questions.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jayoub SSCertifiable Points: 6991 More actions · Answer 4

Thank you very much.

I will give it a try and let you know

Jeff