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!
▾