Sql-server – User has db_owner through AD group but cannot create table

permissionssql-server-2012

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?

Best Answer

There are so many possibilities, but here are a few things to check:

  • that the user is a member of the ADB group in the Active Directory.
  • that ADB exists as a server principal (a login). Example: SELECT * FROM sys.server_principals WHERE [name] LIKE '%ADB';
  • that this login has a user in the database in question: SELECT * FROM myDatabaseName.sys.database_principals WHERE [sid] IN (SELECT [sid] FROM sys.server_principals WHERE [name] LIKE '%ADB');
  • that the database principal (the user) is a member of db_owner (not to be confused with schema ownership)
  • like @mlachman points out in another answer, that the database principal hasn't been specifically denied any actions.

Also,

  • You don't need db_public, db_datareader or db_datawriter on ADB, if it's db_owner. Good housekeeping to clean those memberships up, and it may even help with your problem.

If it's any use to you, I've written a script that compiles all of the permissions in a database. It's available on the Downloads page of my blog, and it's completely "as-is", without any warranty or guarantee.

Related Question