SQL Server Dynamic Script Permissions

Dealing with permissions when modifying data using sp_executesql

Thanks to the impending GDPR we have been implementing a series of changes to our systems, which use Microsoft SQL Server (2008+)

To aid in the speed of development, I have written a bunch of SQL scripts which require the use of sp_executesql commands.  What I had forgotten, however, is the small matter of permissions.

When you run a stored procedure (sproc), the permissions used are those of the user who created the sproc.  Which is great and means that you can run that sproc via a restricted access login.

The problem is that if the sproc uses sp_executesql to run dynamic script, the permissions switch to the restricted access login... the result being that you cannot SELECT, INSERT, UPDATE, DELETE.

The solution we're using involves creating a local WITHOUT LOGIN database user...

CREATE USER dynamicdatauser WITHOUT LOGIN
EXEC sp_addrolemember 'db_datareader', 'dynamicdatauser'
EXEC sp_addrolemember 'db_datawriter', 'dynamicdatauser'

And then allocating that user directly to the sproc...

CREATE PROCEDURE dbo.[MySproc]
  @PARAMS INT
WITH EXECUTE AS 'dynamicdatauser'
AS

To the best of my knowledge, giving the user db_datareader and db_datawriter permissions should not have any security implications as the WITHOUT LOGIN should block any ability for the account to be used outside of the code environment.