Database Simplified Headline Animator

Database Simplified

Wednesday, 26 October 2011

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.

image

 

Goto Menu : View—>Registered Servers

image

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

image 

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

image

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.

image

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.

image

 

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.

No comments:

Post a comment