March 6, 2014 at 6:46 am
We have an application and accompanying database that are about to be moved into production, and I want to ensure that things are locked down as much as possible. What are the best practices etc for securing user accounts in the database? Can they be limited to only run specific stored procedures etc?
March 6, 2014 at 7:52 am
Yes. It can be locked down.
Make sure whichever account is used by the application has appropriate access, exclude anyone else?
For stored-procedures, you can user USE MASTER; GRANT EXECUTE ON [schema].[StoredProcedure] TO [<Account>]
If you need more detailed information, please provide a more detailed description 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 6, 2014 at 7:58 am
Just backing up what's been said. You can get extremely granular on the security, and I'd get as granular as possible. If you can, limit all access to the data to stored procedures. You can even go further and make all logins belong to different schema than the other objects in the database to further isolate their access, in case you miss something. Also, make the default database for the login the database you want them to access. By default it'll be master, but people don't need access to that, even if they can't do anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2014 at 8:36 am
All data access is done through stored procedures, so my thought was to limit the account access to the sp's that it needs. Some of those stored procedures make calls to an another db on the same server, though. Will that be a problem?
March 6, 2014 at 8:46 am
Access will need to be granted to the other databases for the account(s) that are accessing the tables via the stored-procedure. Just check to make sure what the procedure is actually doing to the other database tables and grant SELECT, INSERT, UPDATE, or DELETE based upon what the scripts are actually doing. And yes, you would need to add that same user to the other databases it accesses.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply