SQL Server: Adding A User For a Specific PurposeBy Don Jarrett on Fri June 06, 2014 at 12:11 pm
Today, I was tasked, yet again, to add a single purpose user to our SQL Server database. This purpose was to run one, and only one, stored procedure.
After adding the user, simply deny it all rights to viewing databases:
USE master DENY VIEW ANY DATABASE TO username
Then apply the grant permission to the user on the stored procedure:
USE database GRANT EXECUTE ON Schema.StoredProc TO username
Simple as that! The user can now execute the stored procedure, but can't see any tables/views/stored procedures it's not supposed to!