How to deny ALTER TABLE to DBO

Question

How to deny ALTER TABLE to DBO

Lisandro Fernigrini

Old Hand

Points: 338
More actions
August 17, 2007 at 1:29 pm

#175998

Hello everybody,
I know the title sounds strange so let me describe the problem I have. We have a database with 800+ tables and 2000+ stored procedures, all under the DBO schema. Until now, all users (developers) created their own tables, procedures, functions as they need. They are all granted the OWNER privilege on the database they are working.
Since we have the data model quite stable right now, we want to "freeze" it on the database, and do not allow the develop team to make any change, while allowing them to develop / upgrade the code (sp and functions) they need. The DBA team will be the one that will make any changes to the Data Model structure from now on.
But we found the following problems when trying to obtain this (please consider I'm very new at SQL Server, with Oracle it was easier !!!
1) If we revoked the Owner role and granted the "CREATE FUNCTION" and "CREATE PROCEDURE" privileges, they were not able to create procedure or functions in the DBO schema, and it seems that SQL Server does not have a CREATE ANY PROCEDURE as Oracle does.
2) We keep the OWNER privilege and denied the "CREATE TABLE" one, but even if the "SQL Server 2005 Books Online - DENY Database Permissions (Transact-SQL)" document says that the ALTER privilege is implied in the CREATE one, the user is able to alter any table.
Can anyone help me with this problem?
Thanks in advance,
Lisandro

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

This may come out muddled. If so, I apologize in advance.

Why is it that all of the components have to be under the same ownership? What if you were to recreate all the tables under a different owner? Then, you simply need to provide read / execute / update / delete (record) privileges to the developers, and let them continue to maintain the stored procedures and UDFs under the existing schema. That will require them to EXPLICITLY name the dbo in their procedures. As you migrate to a more fully matured system, and approach a production environment, you will need to have a means of migrating from development into production anyway. Better you define this methodology in a more complete form now, rather than doing it piecemeal. Your methodology may be to use different servers, it may be to use different schema, or some other method. For instance, if the same database owner names are used in different servers, and the developers are locked out of production, then you can still lock down the database architecture and allow them access ONLY to development coding.

Hope this helps. I'm sure others will weigh in with different and probably better ideas.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 2

Lisandro -

Just to clarify Steve's comments, what you need to do is teach your developers to specify the object owner when creating functions and stored procedures.

For example if I am not dbo and I execute "create procedure myprocedure as...." the object will get created as "myusername.myprocedure", if I specify that the object should be owned by dbo as follows "create procedure dbo.myprocedure as..." the object will be created as "dbo.myprocedure".

You may also want to look at the user(s) specified default schema but it's better to get the developers used to the idea of calling out the schema as there is a specific order that SQL Server uses to "hunt" for objects when the schema owner is not specified which can have some interesting consequences when there are multiple objects of the same name (e.g. dbo.myprocedure, myusername.myprocedure, thatotherguysname.myprocedure) in a database - best practice is to explicitly specify schema all the time (e.g. select fielda from dbo.table, exec dbo.spname OR exec myusername.myprocedure).

Joe

Lisandro Fernigrini Old Hand Points: 338 More actions · Answer 3

Steve and Joe:

Thanks for your comments. I appreciate them vey much. The fact is that the users have the default schema set as DBO, but I try to convince them to name the objects explicity, including the owner (dbo) in every case. Most of them are following this rule after I demostrated the problem that may arise if some piece of code exists with the same name in different schemas.

But the problem is that they cannot create procedures like "dbo.writechanges", it says they do not have permission in the dbo schema, unless they have the OWNER privilege. But with that privilege, I can deny them the ability to create a new table but cannot stop them to make changes (alter) to tables.

Thanks anyways, I'll keep trying and will upload anything useful that I may find.

Jeff Burton SSCommitted Points: 1551 More actions · Answer 4

This is why it is a good idea to make the schema/owner for SPs/UDFs to be something other than dbo.

We use a customized role called "dco" (database code owner) for SP/UDF ownership. In SQL2K we have been using this scheme for 4 years. I'm sure it works the same in SQL 2005. You can assign privs "CREATE SP" and "CREATE FUNCTION" (and "CREATE VIEW" if you want) to this role. Omit assigning the "CREATE TABLE" priv to the dco role.

This will allow the developers to code freely but allows the DBAs to lock down the changes to tables under dbo ownership.

Jeff