I created a stored proc that drops and creates linked servers. The following system stored procs are called in my this stored proc:
master.dbo.sp_dropserver
master.dbo.sp_addlinkedserver
master.dbo.sp_addlinkedsrvlogin
It works fine in my dev environment. But when I ran it on the prod db server, the following errors came up:
Msg 15247, Level 16, State 1, Procedure sp_dropserver, Line 20
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 29
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.
After looking around, I found out that the account that runs the stored proc needs the "ALTER ANY LINKED SERVER", "ALTER ANY LOGIN" permission and the "dbcreator" server role. These were not setup on the SQL authentication account that runs the stored proc. So I did the following permission granting in addition to manually set the "dbcreator" role.
USE master;
GRANT ALTER ANY LINKED SERVER TO WarehouseAdmin;
GO
USE master;
GRANT ALTER ANY LOGIN TO WarehouseAdmin;
GO
Now it runs fine.
Generating Sample Data With Copilot In Fabric Dataflows Gen2
-
As a Power Query fan I’m naturally interested in Copilot in Dataflows Gen2.
But since Power Query is already very easy to use and since I’m pretty
experien...
5 days ago
No comments:
Post a Comment