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 ….

  • Advantages of SQLServer vs. MySQL for C#/.NET4 Cloud Applications
  • is there an SQL Server equivalent of PostgreSQL “select * for update” without opening cursor
  • Query taking long time with DISTINCT
  • How to wait for 2 seconds
  • T-SQL Trigger calling SQLCLR Stored Procedure vs SQLCLR Trigger
  • When I Insert Rows into a SQL Server 2005 Table will the order be preserved?
  • 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.

  • SQL Server Table Fails to Update
  • Sargable queries using ISNULL in TSQL
  • Problems with SQL Azure Migration Wizard
  • SQL Server and connection loss in the middle of a transaction
  • How to create a installer to implement a database schema change?
  • How can I copy data records between two instances of an SQLServer database
  • 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.