1

I'd like to provide a "directory" of all the different SQL Servers we have our different DEV, TEST, PROD environments by setting it up in Central Management Servers.

Another DBA said to be cautious of giving this to the group because there is an ability to inadvertently run a query on multiple servers IF they did a "New Query" on a group, instead of an individual server (and this would make sense why they call the feature Central Management Server).

Is there any way to provide this "directory" without having to worry about executing commands on multiple machines?

1

Multi Server queries are available when using registered instances, both with a CMS and with local registration.

There's no way to prevent an SSMS user from using local registrations and running multi server queries. Similarly, if a user has permission to connect to a CMS, you can't prevent them from running multi server queries.

I'd recommend focusing on educating your users on the feature to ensure they understand how registered servers work, and general rules of caution around making sure they are connected to the correct server before running queries.

Additionally, you can ensure that users only have access they need. Users can't run queries if they lack sufficient permissions on the server.

1

There is no way to avoid that once you share CMS. Just be aware that it only stores the list of instances of SQL Servers available, but not necessarily everybody has access to connect in every single instance.

Because the connections that are maintained by a central management server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.