Home / server / Questions / 350095
Accepted
growse
Asked: 2012-01-16 09:05:23 +0800 CST

MSSQL 2008 R2 server roles: do server roles have implicit privileges over *all databases*?

  • 772

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.

sql-server security

1 Answers

  1. Best Answer

    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.

    • 5