Create User In All The SQL Servers

I have found many people questioning on MSDN forum “How to run a query or create a user in all sql server instances” and the answer is using Central Management Server (Registered Servers). see following example of creating a user in all the sql server instances.

I have following three instances of SQL where we need to create a user using registered servers.

 

Goto Menu : View—>Registered Servers

You will have registered servers pane on your screen. See following.

 

You see a “Local Server Groups” folder under Database Engine. You can add all your instances under this group see following how to add ?

Right Click On Local Server Group and Click New Server Registration

You will get following connection dialogue. Using the following dialogue you can register the sql server instance under the local server group. You can register multiple instances of SQL Server or The number of instances on which you want to run a query.

Now after registering the sql server instance you need to open a query window for these registered servers see following.

Now right click on Local Server Group and choose New Query.

 

Now Create Database in all the instances using following query.

Create Database CommonDb

Output :

Command(s) completed successfully. (3 servers)

It means Query is successfully executed on 3 server.

Now Create a User in all the instances

CREATE LOGIN [CommonUser] WITH PASSWORD=N'Admin@123'

Output :

Command(s) completed successfully. (3 servers)

 

So, Central Server management is really useful and nice feature when you are working with multiple and large number of sql instances.