Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    4

    Unanswered: Ownership of Stored Procedures/Functions By Role Other Than dbo

    I would like to enforce the following security policy. This policy is used in our other db systems (Rdb and Oracle), and I'm thinking about how to implement this in SQL Server. (Yes, I'm a DBA.)

    (1) Developers are not allowed to create/alter/delete tables owned
    by dbo. To prevent this, no developers will be granted role db_owner.
    Developers should only be creating/modifying stored procedures/functions.

    (2) All tables will be owned by dbo. DBA's (who have role db_owner
    and server privileges) will be creating/modifying table definitions. DBA's will also be granting individual table priv's to the developers (most likely through the role "dco" below).

    (3) All stored procedures/functions will be owned by a new role "dco"
    (database code owner). All developers will be granted role "dco". No tables should be created in "dco", so role "dco" will be DENY-ed the privilege CREATE TABLE. (I'm also thinking about restricting view creation to dbo by DENY-ing CREATE VIEW.) DBA's will implicitly get access to dco procs/funcs from server privileges.

    Does anybody see any possible problems with this approach? Have you tried anything like this? I've read about "broken ownership chains", but as long as the DBA grants the object privs on the tables to the developers I don't see a problem.

    Thanks in advance for any input.

    Jeff

  2. #2
    Join Date
    Jul 2002
    Posts
    229

    Re: Ownership of Stored Procedures/Functions By Role Other Than dbo

    Who will be the owner of what the DCO's will create?

  3. #3
    Join Date
    Aug 2002
    Posts
    4

    Re: Ownership of Stored Procedures/Functions By Role Other Than dbo

    Role "dco" will own the stored procedures/functions, ie:

    Create Procedure mydb.dco.myproc ...
    Create Function mydb.dco.myfunc ...

    Jeff

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •