Uncategorized

SQL Server Security – Reality Check

by Ben Taylor

Featured Article(s)
Tips for using SQL Server 2005 backup/restore (Part 1)
Here are some helpful tips to perform backup and restore actions in SQL Server 2005.

Building Presentations Based On SQL Server Data?

Stop doing things the old fashioned way – copy, paste, select chart types, format, adjust that font size, change that color… You can have a fantastic presentation quickly and easily by directly integrating your data sources and your presentation tools. Check out XP3 Turbo Templates – a great way to create those presentations and have them accurate, looking great an easy to manage. Get more information here.

SQL Server Security – Reality Check
I wanted to pass along this great message from a reader with their approach and experiences with SQL Server security. We’ve seen this type of approach in several locations.

Dino: "This is typically an approached I’ve seen/used quite a bit lately, and I’d be interested on your views.

Basically, everything is stored-proc driven (whether it’s a web app or other type of application that’s accessing the SQL Server). The stored procs are deployed by running a command line script that runs OSQL statements on sql scripts as a dbowner (which only the DBAs will have access to the credentials). So, a typical sql script that will generate the stored proc [essentially grant access to that stored procedure when it’s created.]


Those stored procs are granted access by specific users used throughout the system. This user can *only* access stored procs. They CANNOT even perform a select statement on any table directly.

Therefore, the user doesn’t need to know the credentials of this “MyWebUser”. All they need to do is have this user (ie same name) on their test environment, but when this goes live the password is different and only the DBAs know this password. So the developers can create the scripts which will be used by the Deployment Procedure without needing to know any credentials other than the name of the user to be used.

This way, if the web server gets compromised and someone finds the user’s SQL credentials, they can still only access the stored procs that were granted to that user – nothing else.

I’d be interested to hear what you think of this approach.

"

Featured White Paper(s)
SQL Server 2008: What to Expect
Microsoft SQL Server 2008 has many great new features that will allow you to develop higher performing, more scalable next-ge… (read more)

EMC Metropolitan Recovery for SQL Server 2005 Reference Architecture
Learn about EMC’s Metropolitan Recovery for SQL Server 2005 solution in this Reference Architecture guide. See how you can us… (read more)

Author: Ben TaylorBen is a Relational Database veteran starting in the early eighties on DEC Vax database systems such as Bloom and Harvest. He has specialized in SQL engines for more than two decades, with a primary focus on Microsoft SQL Server. Ben has worked with small and large databases for both OLAP and OLTP working on systems for many industries such as Medical (Medical Claims and Eligibility Warehouse, HMO Provider Management, Enrollment), Financial (credit card processing, mortgage, stock portfolio management), Manufacturing (Assembly Line and Product Assemblies), Sales, Auto Auctions and more.