db_ddladmin vs db_owner
From what I can tell from what I tested and read up on, for the most part your list looks accurate except db_ddladmin DOES allow you to CREATE SCHEMA. I did confirm that the other security permissions you listed were indeed denied.
Denied with DDLADMIN only:
[ALTER ANY USER]
[BACKUP DATABASE], [BACKUP LOG], [CHECKPOINT]
[ALTER ANY APPLICATION ROLE], [ALTER ANY ROLE]
[DROP DATABASE]
Noting that the. . .
db_datareader will allow SELECT access to all tablesdb_datarwriter will allow INSERT, UPDATE, and DELETE access to all tablesdb_executor will allow EXECUTE access to all executable objects
Additonally, having db_ddladmin role permissions may mean. . .
Note: Since you have so many different versions of SQL Server from 2005 - 2014, it may be best to have a small set of users test
this initially to see who screams to iron out any kinks, etc.
Objects they own with this role will not be owned by DBO so you may have to deal with ownership chaning issues if there's ever a problem with something at this level. I'm not 100% certain that this would be a problem but it's worth mentioning just in case.
Source: Ownership Chains
With this role (may vary depending on the version of SQL Server) they may be able to add SQL security principles defined in the current DB to objects they own still, just not all objects (ones they do not own) nor add a new server-level defined principal to the DB level.
Additionally, not having DBO role permissions may mean. . .
Note: Since you have so many different versions of SQL Server from 2005 - 2014, it may be best to have a small set of users test
this initially to see who screams to iron out any kinks, etc.
Other Considerations
Since you state that this is being reviewed on a case-by-case basis
One of the permissions currently being limited is db_owner permissions.
This permission is being reviewed on a case-by-case basis, but a common change is to replace the db_owner permissions with the following:
- db_datareader
- db_datawriter
- db_ddladmin
- db_executor
Have you considered creating additional custom roles for more "all object" DB-level access that each person needs rather than granting them the db_ddladmin role as that will probably give them more than they actually need to DB level objects as well.
I usually give what's needed exactly and nothing more for them to do their job and if there's a "usual" or "standard" need for DB level object access to all objects in a DB, I create a custom DB role sort of like the the db_executor but see my below example. This way you can grant the people what they really need to ALL DB object in a particular DB if you're not getting object level explicit in your DBs for their security.
----Custom Database Roles
/* CREATE A NEW ROLE -- Execute to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Execute
GRANT EXECUTE TO db_All_StoredProc_Execute
/* CREATE A NEW ROLE -- Alter to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Alter
GRANT ALTER ANY SCHEMA TO db_All_StoredProc_Alter
/* CREATE A NEW ROLE -- View Definition to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_View
GRANT VIEW DEFINITION TO db_All_StoredProc_View
/* CREATE A NEW ROLE - Any schema alter and create procedure permissions */
-- Database specific
CREATE ROLE db_All_CreateProc_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateProc_AlterSchema
GRANT CREATE PROCEDURE TO db_All_CreateProc_AlterSchema
GO
/* CREATE A NEW ROLE - Any schema alter and create table permissions */
-- Database specific
CREATE ROLE db_All_CreateTable_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateTable_AlterSchema
GRANT CREATE TABLE TO db_All_CreateTable_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create function permissions */
-- Database specific
CREATE ROLE db_All_CreateFunction_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateFunction_AlterSchema
GRANT CREATE FUNCTION TO db_All_CreateFunction_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create aggregate permissions */
-- Database specific
CREATE ROLE db_All_CreateAggregate_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateAggregate_AlterSchema
GRANT CREATE AGGREGATE TO db_All_CreateAggregate_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create view permissions */
-- Database specific
CREATE ROLE db_All_CreateView_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateView_AlterSchema
GRANT CREATE VIEW TO db_All_CreateView_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create schema permissions */
-- Database specific
CREATE ROLE db_All_CreateSchema_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateSchema_AlterSchema
GRANT CREATE SCHEMA TO db_All_CreateSchema_AlterSchema
I also wanted to share a db_DDLAdmin_Restriction role you may want to consider to consider creating otherwise with explicit DENY to restrict what db_ddladmin give access to so you could at least create this on the DBs where you grant them this role and set the explicit DENY for the actual object types, etc. you don't want them to have access to.
For example, if you know they will definitely be creating stored procedures and functions, you can exclude DENY CREATE FUNCTION, DENY CREATE PROCEDURE, DENY ALTER ANY SCHEMA.
---Create ddladmin restriction custom DB role
DENY ALTER ANY ASSEMBLY TO db_DDLAdmin_Restriction
DENY ALTER ANY ASYMMETRIC KEY TO db_DDLAdmin_Restriction
DENY ALTER ANY CERTIFICATE TO db_DDLAdmin_Restriction
DENY ALTER ANY CONTRACT TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE DDL TRIGGER TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO db_DDLAdmin_Restriction
DENY ALTER ANY DATASPACE TO db_DDLAdmin_Restriction
DENY ALTER ANY FULLTEXT CATALOG TO db_DDLAdmin_Restriction
DENY ALTER ANY MESSAGE TYPE TO db_DDLAdmin_Restriction
DENY ALTER ANY REMOTE SERVICE BINDING TO db_DDLAdmin_Restriction
DENY ALTER ANY ROUTE TO db_DDLAdmin_Restriction
DENY ALTER ANY SCHEMA TO db_DDLAdmin_Restriction
DENY ALTER ANY SERVICE TO db_DDLAdmin_Restriction
DENY ALTER ANY SYMMETRIC KEY TO db_DDLAdmin_Restriction
DENY CHECKPOINT TO db_DDLAdmin_Restriction
DENY CREATE AGGREGATE TO db_DDLAdmin_Restriction
DENY CREATE DEFAULT TO db_DDLAdmin_Restriction
DENY CREATE FUNCTION TO db_DDLAdmin_Restriction
DENY CREATE PROCEDURE TO db_DDLAdmin_Restriction
DENY CREATE QUEUE TO db_DDLAdmin_Restriction
DENY CREATE RULE TO db_DDLAdmin_Restriction
DENY CREATE SYNONYM TO db_DDLAdmin_Restriction
DENY CREATE TABLE TO db_DDLAdmin_Restriction
DENY CREATE TYPE TO db_DDLAdmin_Restriction
DENY CREATE VIEW TO db_DDLAdmin_Restriction
DENY CREATE XML SCHEMA COLLECTION TO db_DDLAdmin_Restriction
DENY REFERENCES TO db_DDLAdmin_Restriction
GO
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.
Best Answer
A restricted admin should only add users to roles through ALTER ROLE. The ability to grant new permissions to a role or user should be reserved for a real admin.