none
server_principals through Linked servers RRS feed

  • Question

  • Hi,

    I run the query through Linked server and get bad results

    SELECT * FROM remote_server.master.sys.server_principals 

    Is it possible to get correct results from table server_principals and other system security tables remotely?

    I would like to run script and collect security information (logins, user, roles, schemas) from one server instance and avoid running that script on each server.

    Thanks

    Sunday, August 30, 2015 4:12 PM

Answers

  • Hi Jori,

    If you are wanting to pull common system data from a group of servers I would strongly recommend you take a look at Central Management Server instead of linked servers.  You can have one query window open that connects to all servers in a group you establish and it will pull the result set back into one result set for you.

    Here is a quick 4 minutes video I did on this exact problem a few years ago. https://www.youtube.com/watch?v=tZKtV2D4zyk

    Let me know if this works out for you or if you have any questions.

    Regards,
    John

    Sunday, August 30, 2015 4:16 PM
  • And to answer your original question; yes you can query the system tables if your login has sufficient permission. If your query failed, you were probably not connecting with a user holding enough permissions. Permissions required for each table are listed in Books Online. To see everything in sys.server_principals requires the ALTER ANY SERVER ROLE permission.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, September 1, 2015 2:53 PM
  • Yes, you could setup a SQL Agent job that would run powershell that would allow you to run queries against all your servers and store the results or do something else based on the results.

    Here is an example I blogged about a few years ago.

    http://johnsterrett.com/2011/05/12/passed-my-sqluniversity-powershell-midterm/

    Tuesday, September 1, 2015 7:42 PM

All replies

  • Hi Jori,

    If you are wanting to pull common system data from a group of servers I would strongly recommend you take a look at Central Management Server instead of linked servers.  You can have one query window open that connects to all servers in a group you establish and it will pull the result set back into one result set for you.

    Here is a quick 4 minutes video I did on this exact problem a few years ago. https://www.youtube.com/watch?v=tZKtV2D4zyk

    Let me know if this works out for you or if you have any questions.

    Regards,
    John

    Sunday, August 30, 2015 4:16 PM
  • Thanks John, one question, will I be able to schedule the script in SQL job or any other way to do the scheduled query against all the servers?

    Thanks

    Monday, August 31, 2015 9:53 AM
  • And to answer your original question; yes you can query the system tables if your login has sufficient permission. If your query failed, you were probably not connecting with a user holding enough permissions. Permissions required for each table are listed in Books Online. To see everything in sys.server_principals requires the ALTER ANY SERVER ROLE permission.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, September 1, 2015 2:53 PM
  • Yes, you could setup a SQL Agent job that would run powershell that would allow you to run queries against all your servers and store the results or do something else based on the results.

    Here is an example I blogged about a few years ago.

    http://johnsterrett.com/2011/05/12/passed-my-sqluniversity-powershell-midterm/

    Tuesday, September 1, 2015 7:42 PM