up vote 5 down vote favorite
share [g+] share [fb]

I'm setting up a continuous integration solution involving .NET solutions and MSSQL 2008 Databases.

All my build servers sit in an AD security group called Build Servers. I've created the DOMAIN\Build Servers object as an MSSQL login and granted the group the dbcreator and securityadmin server roles. I've not granted any specific database roles, because my understanding is that these server roles should let any AD account in that group create, alter, drop and database or user.

I'm running a sample job that should apply some simple amends to a particular database. However, I'm getting the following error:

Detailed message Cannot open database "awesome-database" requested by the login. The login failed. 
Database.dbschema : Deploy error TSD01234: Login failed for user 'DOMAIN\BUILDSERVER01$'.

DOMAIN\BUILDSERVER01$ is definitely in the Build Servers group, so it should be able to log in and alter any database.

Have I missed something obvious here, or do I have to go and map this login to every single database that exists?

Edit

If I grant sysadmin, it works. Hmmm.

link|improve this question
89% accept rate
feedback

1 Answer

up vote 5 down vote accepted

Server Roles are server wide and apply to every DB, the reason you are having problems is because the roles you have granted don't have the correct rights:

  • Security Admin: can manage server security.
  • DbCreator: can create, alter, drop, and restore databases.

When you say

"I'm running a sample job that should apply some simple amends to a particular database"

I assume this means you are trying to alter an existing database, neither of these roles provide the ability to open or make changes to the content of a Database, in fact the only role that would is the sysadmin role.

So, your options are to either make this group a member of the sysadmin group (which likely has more rights than you want to give), or grant rights on individual databases (either manually or scripted).

The next version of SQL Server will allow the creation of user defined server roles.

link|improve this answer
This seems likely. I guess I got confused because the DbCreator role claims it can alter databases, which I assumed meant things like alter table definitions... – growse Jan 15 at 19:35
I've just read that page now and I can see what that would be confusing. Usually if a user of that role creates the DB then they would also be DBO and so get the associated rights, however I don't believe they can alter the content of a DB that they are not owner on, but they may be able to alter the DB settings. The MS document is a bit confusing on that. – Sam♦ Jan 15 at 19:38
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.