SQL Server Permissions – DDL_admin vs db_owner

permissionssql server

I'm taking over a project that involves removing and limiting permissions of all database users across our server farm. (fun times)

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

I would like to define the exact difference between the two (to inform clients).
However, as far as I can tell, the difference between the two should be:

db_accessadmin permissions
db_backupoperator permissions
db_securityadmin permissions

So in effect they would lose:
[ALTER ANY USER]
[CREATE SCHEMA]
[BACKUP DATABASE], [BACKUP LOG], [CHECKPOINT]
[ALTER ANY APPLICATION ROLE], [ALTER ANY ROLE]
[DROP DATABASE]

Is there anything else that a user would loose once db_owner is replaced by the four roles above?
Does this actually serve much of a purpose security wise?

Best Answer

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 tables
db_datarwriter will allow INSERT, UPDATE, and DELETE access to all tables
db_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.

Not having the DBO role may prevent certain SSMS designer GUI interfaces (SQL Server version varying) from populating or opening without error (e.g. when modifying tables or columns through the GUI) even though doing it via T-SQL works and the permissions are in place. In some versions of SQL Server this may be resolved by allowing GRANT VIEW DEFINITION where this is an issue and it can also just be a warning only on certain versions of SQL Server.

Resources
- You are not logged in as the database owner or as a user that is a member of the db_owner role. You will not be able to save changes to tables that you do not own.
- db_ddladmin Role doesn't allow use of "design" functions in SSMS
  _{"We try to prevent giving users/developers dbo in their QA databases
  as much as we can. One of the problems with this is that they still
  need to be able to create and modify database objects such as user
  tables. Many devs are new to MS SQL and thus tend to stick with the
  GUI (SSMS) for this sort of work. The problem arises when we grant
  them db_ddladmin (not dbo) and they are no longer able to modify
  tables or columns via the table designer GUI. Instead, they have to
  take additional time to learn the TSQL commands and their syntax (that
  they may never need again) or engage the DBA team which takes time
  away from our other activities.}
  
  _{I don't know if this is a bug or a feature request but I consider it a
  bug since the user has sufficient permissions to alter the table via
  TSQL but the GUI gives them messages stating:}
  
  _{"You are not logged on as the database owner or system
  administrator. You might not be able to save changes to tables that
  you do not own." AND "Table [schema].[table] is set to read only,
  user doesn't have enough rights on this table."}
  
  _{A trace seems to point to the check being a is_member('db_owner')
  which will preclude members of db_ddladmin even though they do in fact
  have permissions to modify the object. Microsoft SQL Server Management
  Studio"}
  
  _{_{Posted by Agent DBA on 1/25/2010 at 7:06 AM}}
  
  _{I had a similar issue and managed to solve it by performing the
  following grant}
```
GRANT view definition on schema:: <schemaname> to <username>
```

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

Related Solutions

Sql-server – Permission to create Spatial Index

Microsoft connect ticket was resolved with wording changed:

Please refer to link below (changes have not been made on BOL yet, 1 dec)

https://connect.microsoft.com/SQLServer/feedback/details/706766/books-online-suggested-permission-to-create-spatial-index-incorrect

Sql-server – Permission required to view a database diagram

From the documentation:

Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.

Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.

If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

So it seems that you won't be able to do it with lower roles like db_datareader.

Behind the scenes, here is what Management Studio is calling to drive the list:

CREATE PROCEDURE dbo.sp_helpdiagrams
(
    @diagramname sysname = NULL,
    @owner_id int = NULL
)
WITH EXECUTE AS N'dbo'
AS
BEGIN
    DECLARE @user sysname
    DECLARE @dboLogin bit
    EXECUTE AS CALLER;
        SET @user = USER_NAME();
        SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
    REVERT;
    SELECT
        [Database] = DB_NAME(),
        [Name] = name,
        [ID] = diagram_id,
        [Owner] = USER_NAME(principal_id),
        [OwnerID] = principal_id
    FROM
        sysdiagrams
    WHERE
        (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
        (@diagramname IS NULL OR name = @diagramname) AND
        (@owner_id IS NULL OR principal_id = @owner_id)
    ORDER BY
        4, 5, 1
END

So you can see this matches the documentation.

Now a couple of workaround ideas:

In a Logon trigger, update the principal_id of all diagrams to be the current login. This means they will have access to all diagrams until the next person logs in. Not optimal.
Use a trigger on the sysdiagrams table itself (it's not really a system table), and whenever a diagram is created or updated, add / update a copy for each principal (with their user name appended). Not optimal either, and you could have people overwriting each other's diagrams all day long.

Here is an idea of the second workaround - all you really have to maintain here is a list of the database principals that you want to be able to access the diagrams (you'll also want to have something to clean up diagrams that have been deleted, and also some periodic maintenance that deletes diagrams for principals that have been deleted):

CREATE TRIGGER dbo.sysdiagrams_distribute
ON dbo.sysdiagrams
WITH EXECUTE AS N'dbo'
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p TABLE(principal_id INT, name SYSNAME);

  INSERT @p SELECT principal_id, name
    FROM sys.database_principals
    -- change this list:
    WHERE name IN (N'test_blat_user', N'test_blat_user2', N'dbo');

  UPDATE d 
    SET [version] = i.version, definition = i.definition
  FROM inserted AS i
  CROSS JOIN @p AS p
  INNER JOIN dbo.sysdiagrams AS d
  ON d.name = i.name
  AND d.principal_id = p.principal_id;

  INSERT dbo.sysdiagrams(name, principal_id, version, definition)
    SELECT i.name, p.principal_id, i.version, i.definition
    FROM inserted AS i
    CROSS JOIN @p AS p
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.sysdiagrams WHERE name = i.name
      AND principal_id = p.principal_id
    );
END
GO

After creating a couple of diagrams, here is what an abridged version of Object Explorer looked like for these users:

enter image description here

Now, dbo will collect a whole bunch of copies of diagrams, which maybe isn't necessary, but you probably want those to be the "master" in most circumstances.