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.
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
:CONNECTdirective, 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 [domainuser] FROM WINDOWS; ALTER ROLE [rolename] ADD MEMBER [domainuser]; -- ...'';' 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;
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.