SQL Authentication and Authorization in Software Test Automation, Part II

When using SQL Server Mixed Mode authentication for test automation, you must deal with SQL Users and SQL Logins. These two objects are quite different, but often confused. The essential difference is that SQL Logins are server-scope objects which are used to control authentication and access to the server, while SQL Users are database scope objects which are used to control authorization and permissions on a particular database. Much of the confusion results from the fact that there is a mapping between Logins and Users, and that the names of related Users and Logins are usually the same. Luckily the SQL Server 2000 users-logins model has been improved greatly in SQL Server 2005. Here’s a script template which creates a database for test results storage:


use master



if exists (select * from sys.sysdatabases where name=’dbTestResults’)

 drop database dbTestResultss — note: drops any associated users too



if exists (select * from sys.syslogins where name=’harness’)

 drop login harness



create database dbTestResults



use dbTestResults



create table tblResults


caseid  char(3)  primary key,

result  char(4)  not null, — ‘pass’ or ‘fail’

— other fields here




create login harness with password=’secret’

go — create a server-scope login object for the harness to use


create user harness for login harness

go — create a database-scope user with same name


exec sp_addrolemember ‘db_datareader’, ‘harness’

— allows user ‘harness’ (associated with login ‘harness’)

— to read (using SELECT) any table in dbTestResults

exec sp_addrolemember ‘db_datawriter’, ‘harness’

— allow INSERT and UPDATE



— end of script


The harness code should be fairly self-explanatory. You can use the "create login" command to create a means for a user (actual person or virtual user) to connect to the SQL Server machine. Then you use the new-to-SQL-2005 "create user" command to create a user associated with the login. Finally we can grant permissions to the newly created user, by invoking the sp_addrolemember system stored procedure.

This entry was posted in Software Test Automation. Bookmark the permalink.