How to make a DB user have certain database role membership non interactively in SQL Server Express 2008

First a little introduction.

We have an SQL Server Express 2008 database, which schema is kept in the source control in the form of the scripts, originally created by opening the context menu on the database in the Management Studio and selecting Tasks|Generate Scripts ….

  • Multi-threaded code in CLR Stored Procs?
  • SQL Server DISTINCT pagination with ROW_NUMBER() not distinct
  • How to PRINT a message from SQL CLR function?
  • At maximum how many rows can be in the SQL Server tables
  • Concatenate, how to get different values from a column into one row?
  • How to insert into a temp table the info given by RESTORE FILELISTONLY / HEADERONLY / VERIFYONLY
  • Each developer has a local SQL Server Express 2008 database and there is one central SQL Server 2008 database used by the CI server. Any time a developer changes the schema of the local database, (s)he regenerates the scripts, commits them along with the source code and updates the schema on the central database used by CI server.

    Sometimes, the changes are vast and it is easier to simply delete the entire database and create it from scratch using the scripts, which is really easy.

    BUT, there is one problem. Although, the scripts do create the right database user (say ServerUser), they do not grant that user the db_owner role membership required by the application. The developer must remember to open the properties dialog on the ServerUser user in Management Studio and check the db_owner checkbox in the Database role membership list. Needless to say, we forget this step frequently. I, myself, have just wasted about an hour trying to figure out why the server does not start.

    Being somewhat a naive person, I thought I could manipulate the [master].[sys].[database_role_members] catalog with a simple sql insert statement to add the necessary role membership automatically, as part of the scripts execution. Of course, that failed with the error message Ad hoc updates to system catalogs are not allowed.. Stupid me.

    Anyway, my question is this. Can we have an SQL script to be run when the database and the ServerUser are created, which would make this user have the db_owner role membership for the new database?

    Thanks.

  • How to remove the time portion of a datetime value (SQL Server)?
  • SQL SELECT From two tables (friendship) statement
  • SQL Server : view column datatype does not change when underlying table datatype is changed?
  • SQL Server : Columns to Rows
  • Linq-to-sql: How to actively grab linked objects?
  • getting value from jDateChooser and saving to MS sql DB
  • One Solution collect form web for “How to make a DB user have certain database role membership non interactively in SQL Server Express 2008”

    You can use sp_addrolemember.

    http://msdn.microsoft.com/en-us/library/ms187750.aspx

    EXEC sp_addrolemember 'db_owner', 'username'
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.