We've setup a SQL Server 2012 Enterprise, and through certain domain user groups, users are granted access to several databases.
For instance, group 'ADB' has for several databases following rights:
Server role: public
User mapping: db_datareader, db_writer, db_owner, db_public
Now, our application sometimes needs to execute certain queries. One of them was a CREATE TABLE statement. When a user of the group 'ADB' opens the application of us, in the background the script is run to CREATE TABLE. But it doesn't do that, because the user has no right to create. I don't understand because that user is part of a domain group that is DB_OWNER.
If I run a T-SQL command that shows all the members of
db_owner, I don't see the AD group 'ADB' (as I would have expected) for those databases, but I do see 'sa'. Could this be the cause of my problem?
Hundreds of databases have rights assigned through server roles and user mapping, manually through SSMS. So it will be a lot of work fixing those things, but maybe it can be done through scripting. All I want to know is, why a user can't do a create table if he is part of a domain group that is db_owner of a database?