Hi,

I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.

I have table "dbo.instances" with list of instances. 
I have databases from "sys.databases". 

How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.

Thanks for advise

Powershell script may fit better for this type of tasks.

• Proposed as answer by Naomi NModerator Thursday, August 27, 2015 4:08 PM

Hi Jori5,

It is not possible to interact among SQL Server instances in TSQL, unless linked servers are set or with xp_cmdshell. If you want to get principals information from datases of different instances, you would have to set linked servers to those instances and running concatenated query with instancesname and databasename dynamically specified in a cursor. That is obviously not a good way.

As Jingyang suggested, Powershell would be a better option in this case. See Using Powershell to loop through a list of SQL Server Databases.

If you have any question, feel free to let me