The sa account was recently locked out on my company's public-facing database due to numerous failed login attempts from an unknown user. I have their IP address in the logs but it scared the hell out of me.
I changed all my passwords and am in the process of encrypting all the sensitive data.
How do I restrict a SQL Server Authentication user to only be able to execute certain stored procedures, but do nothing else (not see anything nor even be able to do Select * From [SomeTable])?
Update:
I ended up setting up a whitelist of IPs for the firewall, creating random 90 character passwords, setting db_denydatawriter and db_denydatareader and granting Execute for specific stored procedures for specific users.
It's not safe to open SQL Server to the internet. A minimally responsible solution would place a VPN or a web server in between.
Having said that, you can remove a user from all groups (including public) and then grant only rights on specific stored procedures. Without db_datareader, a user cannot select * from SomeTable.