debugMinor
User has db_owner through AD group but cannot create table
Viewed 0 times
cannotgroupcreatedb_owneruserbuthasthroughtable
Problem
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
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?
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?
Solution
There are so many possibilities, but here are a few things to check:
Also,
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.
- 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_datareaderordb_datawriteron ADB, if it'sdb_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.
Context
StackExchange Database Administrators Q#128580, answer score: 2
Revisions (0)
No revisions yet.