THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

Database Security Testing

This blog has moved! You can find this content at the following new location:

Published Tuesday, November 2, 2010 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



RichB said:

Just remember that sql logins are passed across the network with passwords in plain text.

If you haven't ever done it, try running wireshark against a sql login... see if you can guess the password.

November 2, 2010 9:56 AM

andyleonard said:

Excellent point Rich,

  Hopefully the Development environments are burried behind firewalls inside the enterprise, but I did not specify that in my post.

  Thanks for pointing that out!

:{> Andy

November 2, 2010 10:17 AM

Alexander Kuznetsov said:

Hey Andy,

Suppose I am using only integrated security on my test server. Suppose I have a user testUser with password myPwd in AD. Can you show me how to change the connection string so that the connection uses testUser? Thanks.

November 2, 2010 11:05 AM

andyleonard said:

Hi Alex,

  That part of my post is worded poorly. In fact, I injected the line about using AD after I'd written everything else, because I didn't originally consider instances not using Mixed Mode.

  i sincerley appreciate you pointing this out. I need to do a better job proof-reading in the future.

:{> Andy

November 2, 2010 11:13 AM

andyleonard said:

I updated the Security Testing portion of this post for clarity. Thanks again Alex.


November 2, 2010 11:20 AM

Alexander Kuznetsov said:

No problem, Andy. Happy programming and unit testing!

November 2, 2010 5:31 PM

granadaCoder said:

I think the principle is "Use a limited account to conduct testing".

My personal experience is that a sql-authentication-login in DEVELOPMENT will flush out the issues early.

Obviously (as suggested) the plain-text password is an issue.  For me, plain text passwords in DEVELOPMENT is not an issue.  However, I cannot use sql-authentication in production.


Developer Environment (databases not on my local machine but still before QA gets to them)=sql-authentication

QA environment=sql-authentication

Staging=Windows Authentication

Production=Windows Authentication

If you are not running mixed mode, then (as already suggested) creating a windows-account and giving it permissions is the way to go.

My preference is to "code up" permissions.

GRANT EXECUTE ON [MySchema].[uspEntityGetAll]

TO someSqlAuthenticationUser , [MyDomain\myGroup] , [MyDomain\_mySpecificUser]


Doing this up front (with a limited account) will prevent deployment issues in the area of "EXECUTE permissions denied (blah blah blah) on such such object".  Which is the tell-tale sign of bad deployment planning.

Of course this is (yet) another place where sqlcmd (and running in sqlcmd mode) is a clear winner. (And of course I learned this type of approach from Andy during one of his presentations).

You code to a $(Variable) instead of a hard-coded value.


MYLIMITEDACCOUNT could be set to any one of the following:


, [MyDomain\myGroup]

, [MyDomain\_mySpecificUser]


:setvar MYLIMITEDACCOUNT "someSqlAuthenticationUser"

GRANT EXECUTE ON [MySchema].[uspEntityGetAll]



If you're looking for "sqlcmd mode", it on SSMS menu near "Query / Results To" (at least in SSMS 2008 that's one place it is).

Hopefully, people are thinking ~about deployment (way) ~before the deployment actually occurs.


Security testing is essential and should be done as early in the development process as possible.//

True DAT!!

November 12, 2010 4:18 PM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement