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 ....
我们有一个SQL Server Express 2008数据库,该模式以脚本的形式保存在源代码管理中,最初是通过打开Management Studio中数据库的上下文菜单并选择Tasks | Generate Scripts ....
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.
每个开发人员都有一个本地SQL Server Express 2008数据库,CI服务器使用一个*SQL Server 2008数据库。每当开发人员更改本地数据库的模式时,他都会重新生成脚本,将它们与源代码一起提交,并更新CI服务器使用的*数据库上的模式。
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.
但是,有一个问题。虽然脚本确实创建了正确的数据库用户(比如ServerUser),但是它们不会向该用户授予应用程序所需的db_owner角色成员资格。开发人员必须记住在Management Studio中的ServerUser用户上打开属性对话框,并检查Database role membership列表中的db_owner复选框。不用说,我们经常忘记这一步。我,我自己,浪费了大约一个小时试图找出服务器无法启动的原因。
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.
作为一个天真的人,我以为我可以操纵[master]。[sys]。[database_role_members]目录,用一个简单的sql insert语句自动添加必要的角色成员资格,作为脚本执行的一部分。当然,失败的错误消息不允许对系统目录进行即席更新。愚蠢的我。
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?
无论如何,我的问题是这个。我们是否可以在创建数据库和ServerUser时运行SQL脚本,这将使该用户具有新数据库的db_owner角色成员身份?
Thanks.
1 个解决方案
#1
3
You can use sp_addrolemember.
您可以使用sp_addrolemember。
http://msdn.microsoft.com/en-us/library/ms187750.aspx
EXEC sp_addrolemember 'db_owner', 'username'
#1
3
You can use sp_addrolemember.
您可以使用sp_addrolemember。
http://msdn.microsoft.com/en-us/library/ms187750.aspx
EXEC sp_addrolemember 'db_owner', 'username'