question

Anup_Ag avatar image
Anup_Ag asked

SQL Server User list extraction automation

I want to extract the list of users having access to a particular MSSQL DB and automate the task. I am using the below query: select name from sys.database_principals a left outer join sys.database_role_members b ON a.principal_id=b.member_principal_id where is_fixed_role <> 1 The above query gives a list of users that has some global group names too. I also have to extract the users present in each global group. I can do that with the help of dsget command in command prompt. dsget group "CN= ,OU=Application,OU=Groups,DC= ,DC=<>,DC=com" -members -expand Is it possible to automate these two steps through a single SQL Agent job and present the data in an excel sheet.
sql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Dave_Green avatar image
Dave_Green answered
Almost certainly yes, but I believe it would be somewhat of a fiddle, to get the information from the output of the command prompt, back into an excel sheet. I would suggest looking at PowerShell to do this for you. I'm no expert, but I do believe it can be persuaded to run an SQL query, and use each row returned as a parameter to a query against the active directory. I believe it can also then output into Excel. Powershell can then be called from SQL Agent. Sorry I can't provide a detailed example - others may be able to do so.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.