March 5, 2009 at 1:44 pm
I have a user called DOM\my_sysadmin that is part of the sysadmin fixed server role.
I run the following and it works great:
EXECUTE AS LOGIN = 'DOM\my_sysadmin'
EXEC master.sys.sp_addlinkedserver @server = N'ServerA', @srvproduct=N'SQL Server'
GO
I have created a procedure that uses a table (dbo.SQL_Servers) populated with server names.
CREATE TABLE dbo.SQL_Servers(
server_name varchar(128))
GO
INSERT INTO dbo.SQL_Servers
VALUES('ServerA')
GO
If a server name in the table is not a linked server on the current server, I want to add it. When I run the procedure, I get error # 15247 for error_procedure "sp_msaddserver_internal" that states, "User does not have permission to perform this action". Here is a sample of the code:
CREATE PROC dbo.add_new_server
WITH EXECUTE AS 'DOM\my_sysadmin'
AS
DECLARE @server_namevarchar(128)
DECLARE server_cursor CURSOR FAST_FORWARD FOR
SELECT server_name
FROM dbo.SQL_Servers
WHERE Server_Name NOT IN (SELECT name
FROM sys.servers)
OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @server_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC master.sys.sp_addlinkedserver @server = @server_name, @srvproduct=N'SQL Server'
FETCH NEXT FROM server_cursor INTO @server_name
END
CLOSE server_cursor
DEALLOCATE server_cursor
I have even used the suser_name() function, in both cases, to verify that the command is running as DOM\my_sysadmin and it is. This works fine when I run it as myself (I'm also a sysadmin).
Any ideas why this is not working?
Thanks,
hawg
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
March 5, 2009 at 2:21 pm
Verify that user 'DOM\my_sysadmin' is associated with Login 'DOM\my_sysadmin' in database in which sp is executing.
March 5, 2009 at 2:57 pm
I think what is happening is that the stroed procedure is executing as the SysAdmin user but when it executes anything with the exec command it opens a new connection that defaults back to the original logins permissions. You could use Profiler to confirm that.
Instead of doing the aliasing inside the procedure, alias the login before calling the procedure and then it should work fine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply