SQL Server restrict user to selected stored procedures only

Question

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.

Probably off topic, but you know it's generally considered a really bad idea to leave a database open to the public internet, right? Secured or not. — Chris Shain
– Chris Shain, Commented Mar 27, 2012 at 20:40
its not off topic. It is dead-on. Talk to your network admin. simply said, unless its inside your network on a vpn or its inside your network, no direct connection to sql-server. Also, 'sa' should be disabled on every sql server. — jerry
– jerry, Commented Mar 27, 2012 at 20:50

Remus Rusanu · Accepted Answer · 2012-03-27 20:45:57Z

3

Attacks against an unprotected sa occur all the time, is part of the arsenal of every botnet and malware scan kit out there.

The only restrictions you have at your disposal are permissions and grants. There is no mechanism to restrict what is being executed from a login (there is no mechanism similar to, say, how a ssh tunnel can be restricted to only certain commands). So it really boils down to permissions and strong passwords.

answered Mar 27, 2012 at 20:45

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andomar · Accepted Answer · 2012-03-27 20:41:56Z

0

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.

answered Mar 27, 2012 at 20:41

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

SQL Server restrict user to selected stored procedures only

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related