SqlCredit – Part 4: Schema and Procedure Security

This is part 4 of a series. If you have not read part 3, you can find it here.


In this month’s installment, we will add security to the existing code and add new unit tests to prove that security.


Database Security: It’s Not Just For Administrators


Too many applications are built assuming that security needs to be handled at the web tier or middle tier only, ignoring details like object permissions in the database. The other myth is that database security is just a matter of administration.


I am proposing a set of design security standards here that I hope you will scrutinize closely. If you believe they could be better, speak up!


Security Logins, GRANTs


For a web application like SqlCredit, users will not reach the database with their Windows Authentication credentials. The user will log in at the web tier, and their application credentials will accompany their session. When the application accesses the database, it will use Windows Authentication (a.k.a. integrated security). The login used by the application to access the database should have limited privileges. There are many good articles that detail those specific privileges, so I won’t go into that detail here. For in-depth coverage, see Marcin Policht’s two series on SQL Server security, covering both SQL Server 2000 and 2005.


It is a best practice to remove the public role from production servers, so there are no specific denies regarding the public role.


Grant Privileges to Stored Procedures and Functions, Not Tables


The security we are setting up here is for the primary users of the application: the on-line customers. We are not handling reporting capabilities or back-office work (at least for now).


Because the application will be accessing the database through our published APIs (stored procedures and functions), those are the only objects that need to have GRANT statements. There is no need for direct access to the tables by the OLTP application login.


I know that privileges are not granted automatically, but I still like to do explicit DENYs to tables and views. I have gotten so much negative feedback from our implementation teams that I have finally been convinced not to DENY SELECT on tables and views. In a perfect world, I would do that, but I won’t for this code. If you control both the design and the implementation, then you may decide to lock down even SELECT.


An example of a DENY statement is here:

    DENY DELETE, INSERT, REFERENCES, UPDATE ON dbo.Account TO SqlCreditAppSqlRole;

An example of a GRANT statement is here:

    GRANT EXEC ON dbo.CreateAccount TO SqlCreditAppSqlRole;

You probably already understand that I can create an INSERT procedure that inserts records into the Account table, GRANT EXEC on that stored procedure, and the INSERTs will work through the stored procedure but will not work directly (ad-hoc).


An important consideration here is to balance the number of distinct groups/roles you create against the complexity of managing those credentials. For an application like SqlCredit, I would recommend setting up distinct groups/roles for the following functions:



  • OLTP access
  • Reporting
  • Purging

The user that can update a single card should not be the same user that can purge a month’s worth of old purchase data.


Feedback: What do you think about this design? I have created a new thread in the forum to discuss security, and I would appreciate any feedback. Certainly, there is more to a complete implementation’s security plan than this. Besides encrypting certain personal data (which I will add in a later installment), what do you think is missing from the application design?


Remember that at this point we are still supporting both SQL Server 2000 and 2005, so we cannot yet use any 2005-specific security features. However, please do comment on changes you would expect once we move this series to 2005-only security.


Windows Authentication Group and User


Before running the scripts, the Windows Authentication group needs to be set up. Since I am doing this development and writing on a laptop that is not connected to anything, I will create the group and user as local accounts. Here are the steps to set up the Windows Auth group and user:



  • Set up SqlCreditAppWinAuthGp
  • Set up SqlCreditWinAuthUser
  • Add user SqlCreditWinAuthUser to group SqlCreditAppWinAuthGp

All the rest of the set-up is done by the scripts.


Testing Object Access Using TSqlTest


Setting up object security is not an easy task, but to be confident that it works correctly, it must be tested. (Of course, to be continually confident requires continuous integration and automated testing.)


TSqlTest allows us to create a separate script to be run as the SqlCreditWinAuthUser Windows login to verify that



  • This login cannot directly affect any tables (INSERT, UPDATE, DELETE).
  • This login can execute the appropriate stored procedures.

The attached test script (UnitTestsTestSecurityForAppUser.sql) is called by a batch file (Tests_LoginAsSqlCreditWinAuthUser.bat) while logged in as the application user (SqlCreditWinAuthUser).


When the script tries to INSERT directly into the Account table, error 229 is raised.

Msg 229, Level 14, State 5, Line 89
INSERT permission denied on object ‘Account’, database ‘SqlCredit’, schema ‘dbo’.

Because this is the exact error expected, the test passes. Any other error number will cause the test to fail. The script then tests that the application user can execute the appropriate stored procedures.


There are now two different batch files that must be run as two different users to complete the full test suite. This requires a bit more scripting but provides confidence that the security is working properly.


For Next Time


Next time, we will fill out much more of the application: cards, vendors, and purchases.


References and Further Reading



Download the scripts for this article.


» See All Articles by Columnist Rob Garrison

Rob Garrison
Rob Garrison
Rob Garrison is a database architect at Fiserv in Portland, Oregon specializing in OLTP design and development. He has over twenty years of IT experience, with over ten years in database development and architecture.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles