Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

deny execute permissions on sp_linkedservers for particular login Expand / Collapse
Author
Message
Posted Friday, August 2, 2013 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:51 AM
Points: 27, Visits: 112
Hi friends,

Is there any way to deny execute permission on sp_linkedservers for particular sql login.

Thanks in advance.
Post #1480515
Posted Friday, August 2, 2013 3:32 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:57 AM
Points: 802, Visits: 717
;May I ask why you want to do this?

In any case, the answer is yes. You need to create an explicit user for the login in the master and then DENY EXECUTE on sp_linkedservers to this user. This script illustrates:

USE master
go
CREATE LOGIN linkan WITH PASSWORD = 'ddgfd()'
CREATE USER linkan
go
DENY EXECUTE ON sp_linkedservers TO linkan
go
USE tempdb
go
EXECUTE AS LOGIN = 'linkan'
go
EXEC sp_linkedservers
go
REVERT
go
USE master
go
DROP USER linkan
DROP LOGIN linkan



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1480630
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse