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

You can use sp_addrolemember msdn.microsoft.com/en-us/library/ms18775... EXEC sp_addrolemember 'db_owner', 'username.

You can use sp_addrolemember. msdn.microsoft.com/en-us/library/ms18775... EXEC sp_addrolemember 'db_owner', 'username.

Excellent. Just what I was looking for. – mark Dec 17 '09 at 13:01.

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.

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?

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.

Related Questions

» About WebKnox
» Contact
» WebKnox Blog
» RSS
» Newsletter
» Twitter
» API
» API Showcase