Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When adding users are roles to DB servers I often use the "Script this action" function from the GUI. I then just go to "Connection :: Change Connection" to do the same on my other servers.

Is there a way I can specify the connection in the scripted action so I don't have to do that second Change Connection step?

share|improve this question
add comment

3 Answers

up vote 11 down vote accepted

No way to do this as part of a script from SSMS, but you do have two options.

One thing you can do is use SQLCMD mode and the ::connect command in order to have a script that will connect to multiple servers and run the script. This works well if you save the script for the user and use the :r command to load the script from a file.

Another thing you can do is configure a Central Management Server and then run your script against multiple servers at once.

share|improve this answer
1  
"Central Management Server". ah, that's what I don't use currently... –  gbn Mar 22 '11 at 17:11
    
yeah, it is a hidden gem for things like this, much better than SQLCMD scripts. –  SQLRockstar Mar 22 '11 at 17:18
add comment

Actually, it is possible from within T-SQL, but you have to meet a certain set of conditions and jumpt through a few hoops.

  • First, you need to enable remote queries (OPENDATASOURCE/OPENROWSET) on the server from which the queries will be run.
  • Second, you need to make sure that the target servers have remote access enabled.
  • Third, you will need to make heavy use of dynamic SQL so that you can "inject" T-SQL code into the database engine of the target server to be executed.

Here is a sample script that will allow you to leverage the CMS to automate SQL tasks.

/****************************************/

/* Global change password script */

/* */

/* This script changes the password for a SQL login on all servers */

/* managed by a Central Management Server. It assumes that the login */

/* exists on all servers, and that all servers are SQL 2005 or later. */

/****************************************/

DECLARE @nServer NVARCHAR (128) -- Variable to hold the instance name retrieved from the CMS

DECLARE @nSQL NVARCHAR (4000) -- Variable to hold dynamic SQL

DECLARE @ServerFetch INT -- Variable to hold the fetch status. In SQL 2005, the @@FETCH_STATUS

                            -- variable is scoped at the system level, so if another process is also

                            -- using a cursor the @@FETCH_STATUS variable will be set according to

                            -- that operation. This allows us to store a persistent value.

DECLARE curServer CURSOR LOCAL STATIC FOR -- Declare the cursor with the LOCAL and STATIC options, and

                                       -- retrieve the list of server names from the Central Management

                                       -- Server. The value in the [sysmanagement_shared_server_groups_internal]

                                       -- table is user-defined; for purposes of this example we have

                                       -- created a group named "SQL2008".

SELECT DISTINCT

s.server_name AS 'ServerName'

FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source = CMS1\Management; Integrated Security = SSPI').msdb.dbo.sysmanagement_shared_server_groups_internal g

INNER JOIN OPENDATASOURCE ('SQLOLEDB', 'Data Source = CMS1\Management; Integrated Security = SSPI').msdb.dbo.sysmanagement_shared_registered_servers_internal s ON g.server_group_id = s.server_group_id

WHERE g.name = 'SQL2008'

ORDER BY s.server_name

OPEN curServer

FETCH FIRST FROM curServer INTO @nServer -- Retrieve the first row

SET @ServerFetch = @@FETCH_STATUS -- Store the status of the fetch operation

WHILE @ServerFetch = 0 -- If the fetch was successful, we enter the loop. Otherwise

                                           -- execution passes to the statement following the END statement.

BEGIN

-- Build the dynamic SQL to alter the password for the SQL login.

SET @nSQL = 'EXEC OPENDATASOURCE (''SQLOLEDB'', ''Data Source = ' + @nServer

    + '; Integrated Security = SSPI'').master.dbo.sp_executesql N''ALTER LOGIN SQLLogin WITH PASSWORD = ''''<enterStrongPasswordHere>'''''

-- Execute the dynamic SQL.

EXEC sp_executesql @nSQL

FETCH NEXT FROM curServer INTO @nServer    -- Retrieve the next row.

SET @ServerFetch = @@FETCH_STATUS          -- Store the status of the fetch operation.

END

CLOSE curServer -- Close the cursor.

DEALLOCATE curServer -- Remove the cursor from memory.

share|improve this answer
add comment

No. Only the database by USE Database. A connection isn't scriptable.

SSMS 2008 (?) and other tools offer the ability to "run on multiple servers". Sorry, I don't use this feature in my current role so don't have this problem.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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