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
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?
If I grant
sysadmin, it works. Hmmm.