Adding sql users via linked servers

Question

I need to add a windows user and set role to sysadmin for a bunch of linked servers and cannot figure out the syntax to make the current linked server active for the query to add the user and role.

Any suggestions? I have only seen one example of this on the web but it does not work for SQL 2008.

Answer 1

If you mean what I think you mean, no, you can't say something like USE [LinkedServer]; in an SSMS query window. There are alternatives:

• You can change the current connection to a different server by right-clicking the query window, Connection, Change Connection. That will allow you to change connections to instances you can see, which may not be the same as the set of the linked servers the target can see, and it doesn't provide a way to do so programmatically.
• Same issue with right-clicking a group of Registered Servers (Ctrl + Alt + G) and choosing New Query - those are registered servers on your client workstation, not linked servers set up in the target instance.
• You can use SQLCMD mode and use the :CONNECT directive, but that presents the same issues as above.

I don't think either of those is what you're after. You can build the set of commands you would need to make the target instance execute a command against multiple linked servers this way:

DECLARE @sql nvarchar(MAX) = N'';
SELECT @sql += N'
 EXEC ' + QUOTENAME(name) 
 + N'.master.sys.sp_executesql 
 N''CREATE LOGIN [domain\user] FROM WINDOWS;
 ALTER ROLE [rolename] ADD MEMBER [domain\user];
 -- ...'';'
 FROM sys.servers
 WHERE is_linked = 1;
PRINT @sql;

You can use additional filters to remove certain linked servers using other criteria, or otherwise modify the commands, then copy and past them to execute. You can also just say the following if you trust it:

EXEC sys.sp_executesql @sql;

Since the PRINT command will only support 8k, which means a limit on the number of servers (actual number will depend on the length of the command and the length of the names of the linked servers).

Answer 2

Can you post the query you're trying to use?

Most of the time, if I'm trying to execute a SQL Statement over a linked server, I build my SQL Query as a variable and use EXECUTE AT.

DECLARE @SQLQuery VARCHAR(1000) = 'ALTER TABLE [tableName]
 ADD [columnName] VARHCAR(50);'
EXECUTE (@SQLQuery) AT [LinkedServerName];

You'll need to create a user (on the remote server) to use with the linked server if you haven't already.