June 13, 2018 at 2:25 am
Hi,
I wondered if someone could answer a question for me please
Is it possible to turn off sp_executesql on a server? or is it just one of those things that should be properly managed by permissions and access?
Thanks,
Nic
June 13, 2018 at 2:30 am
Nic
I don't think there's a setting whereby you can turn it on or off. All members of the public role have access to the stored procedure, according to the documentation. You could try denying EXECUTE on it to public. I've never tried that myself, though.
John
June 13, 2018 at 3:20 am
Thanks for the reply, you are correct running the following prevents execution.
DENY EXECUTE ON sp_executesql TO testuser
Thanks,
Nic
June 13, 2018 at 4:50 am
That won't stop them using EXEC though:
USE master;
GO
CREATE LOGIN TestLogin WITH PASSWORD = '123', CHECK_POLICY = OFF;
GO
CREATE USER TestLogin FOR LOGIN TestLogin;
GO
DENY EXECUTE ON sp_executesql TO TestLogin;
GO
EXECUTE AS LOGIN = 'TestLogin';
GO
DECLARE @SQL nvarchar(max) = N'SELECT 1';
EXEC sp_executesql @SQL; --This fails
GO
DECLARE @SQL nvarchar(max) = N'SELECT 1';
EXEC (@SQL); --This works
GO
REVERT
GO
DROP USER TestLogin;
DROP LOGIN TestLogin;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 13, 2018 at 6:18 am
I wouldn't deny execute on that procedure, but then I make extensive use of it myself in both scripts and stored procedures.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply