Deny Alter table for user with DBO? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Deny Alter table for user with DBO?

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-02-28 : 11:33:25

I am able to successfully deny create table for a user with DBO access but they still have the ability to Alter table. I tried to deny that as well but get this error:

Server: Msg 165, Level 16, State 2, Line 1
Privilege ALTER TABLE may not be granted or revoked.

This user must be DBO. I can but am not allowed to change it.

Is there a way to also deny ALTER TABLE?

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-28 : 11:52:36

From a security point of view, there doesn't seem to be much point to this. Since they are DBO, they can give themselves CREATE TABLE or any other database privilege if they want.

Maybe there is another way to setup security. Please explain what the requirements are that make DBO access necessary.

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-28 : 13:40:10

Dbos can do anything in the database where they are dbo. There's really nothing you can do if they must dbo. I'm sure there is a way to not give them dbo and still give them all of the permissions that they require. Well unless you want them to create dbo objects like stored procedures. Is this for developers?

Tara Kizer
aka tduggan

activecrypt
Posting Yak Master

165 Posts

Posted - 2006-02-28 : 23:29:40

Hi,
as tara said DBO can assign permissions they want in the DB where they are dbo so it cannot be fullproof , read below link and please clearly describe what you wants to achieve to get a good answer .
http://msdn2.microsoft.com/en-us/library/ms189612.aspx

www.databasejournal.com/features/mssql/article.php/3370701

msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_7e43.asp

msdn.microsoft.com/library/en-us/adminsql/ad_config_09yd.asp

www.sqlservercentral.com/columnists/dasanka/basicsofc2auditing.asp

Andy Davis
Sql Shield Team
--------------------------------------------
SQL Server Encryption Software
http://www.sql-shield.com

mr_mist
Grunnio

1870 Posts

Posted - 2006-03-01 : 03:30:01

quote:

This user must be DBO.

It doesn't sound like it.

Why must they be DBO?

-------
Moo. :)

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-03-01 : 09:54:52

quote:
Originally posted by mr_mist

quote:

This user must be DBO.

It doesn't sound like it.

Why must they be DBO?

-------
Moo. :)

They had to be because that's what orders I was given.

pootle_flump

1064 Posts

Posted - 2006-03-01 : 10:08:28

quote:
Originally posted by SQLServerDBA_Dan

This user must be DBO. I can but am not allowed to change it.

Is there a way to also deny ALTER TABLE?

Surely this is an oxymoron - it is a bit like being God but with limited powers. If someone owns the database they will be able to change it. If they can't change it they aren't the owner.

I'm not even sure what other problems you are likely encounter as, like probably everyone else here, I've never tried to hobble a dbo account. You should really start with the permissions a user require and tailor an account round that rather than the other way round.

quote:
Originally posted by SQLServerDBA_Dan
They had to be because that's what orders I was given.

Sounds more like a person problem than a technical one IMHO...

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-03-01 : 10:11:23

quote:
Originally posted by tkizer

Dbos can do anything in the database where they are dbo. There's really nothing you can do if they must dbo. I'm sure there is a way to not give them dbo and still give them all of the permissions that they require. Well unless you want them to create dbo objects like stored procedures. Is this for developers?

Tara Kizer
aka tduggan

Yes this is for developers. We want them to create SP's under the DBO schema and then grant themselves permissions to those SP's. I was going to give them the db_securityadmin db role to fix that issue but the other issue is that they cannot use the sp_changeobjectowner proc to change the schema to dbo. The thing that needs to happen is they need all the functionality of dbo without create or alter table. It 100% does not matter if they have the ability to grant themselves permission to create or alter tables because if they do they will be fired. We face a problem that newly hired developers are not following the development procedures and are creating or changing the DDL without letting the DBA group know so we can update the ER Studio models. I know security pretty well. It's quite simple but some things Microsoft does like not allowing deny on alter table is really silly. If anything I would think that Alter table would be tied to Create table but it's not.

So, I'm sure the answer is "You can't", but I'll ask again: Is there a way to give dbo access and deny alter table?

Thanks,
Daniel

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-03-01 : 10:19:45

quote:
Originally posted by pootle_flump
I'm not even sure what other problems you are likely encounter as, like probably everyone else here, I've never tried to hobble a dbo account. You should really start with the permissions a user require and tailor an account round that rather than the other way round.

There are a few reasons you'd want to do this, but I guess jr dba's that only have text book experience probably would not understand.

quote:
Originally posted by pootle_flump

quote:
Originally posted by SQLServerDBA_Dan
They had to be because that's what orders I was given.

Sounds more like a person problem than a technical one IMHO...

Sounds like you live in a box where you have no management and you control your own destiny. There is no need to be rude sir. The technical problem is that I need dbo with deny alter table. If it can't be done they say it can't rather than tossing out rude comments that are both not helpful and not welcome.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-01 : 11:02:48

if it's development, give them dbo, they are responsible
if it's production, don't even provide them access to the databases, the applications should have been tested before going to productions

if you are really in need of this "feature" I suggest you revoke dbo and create an interface for them where they can create/modify sprocs, udfs and views

it's doable... i did it...

--------------------
keeping it simple...

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-03-01 : 11:43:16

quote:
Originally posted by jen

if you are really in need of this "feature" I suggest you revoke dbo and create an interface for them where they can create/modify sprocs, udfs and views

it's doable... i did it...

/Cheer! See that's what I'm talking about a comment that is productive and helpful. That could be as simple as a batch file that calls osql or as involved as a .NET app. I do believe I'll head down that route. Then I don't even need to give them db_securityadmin.

pootle_flump

1064 Posts

Posted - 2006-03-01 : 12:39:22

quote:
Originally posted by SQLServerDBA_Dan

quote:
Originally posted by pootle_flump
I'm not even sure what other problems you are likely encounter as, like probably everyone else here, I've never tried to hobble a dbo account. You should really start with the permissions a user require and tailor an account round that rather than the other way round.

There are a few reasons you'd want to do this, but I guess jr dba's that only have text book experience probably would not understand.

quote:
Originally posted by pootle_flump

quote:
Originally posted by SQLServerDBA_Dan
They had to be because that's what orders I was given.

Sounds more like a person problem than a technical one IMHO...

Sounds like you live in a box where you have no management and you control your own destiny. There is no need to be rude sir. The technical problem is that I need dbo with deny alter table. If it can't be done they say it can't rather than tossing out rude comments that are both not helpful and not welcome.

Lol - no offense was intended - I admit my post was not the most constructive however it was not meant as an insult - I will side step your own little insult though.

I still hold, given your responses, that your problems are procedural not technical given that junior (or for that matter any) developers are able to alter the database schema without the DBAs knowledge.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-01 : 12:52:37

quote:
Originally posted by SQLServerDBA_Dan

quote:
Originally posted by tkizer

Dbos can do anything in the database where they are dbo. There's really nothing you can do if they must dbo. I'm sure there is a way to not give them dbo and still give them all of the permissions that they require. Well unless you want them to create dbo objects like stored procedures. Is this for developers?

Tara Kizer
aka tduggan

Yes this is for developers. We want them to create SP's under the DBO schema and then grant themselves permissions to those SP's. I was going to give them the db_securityadmin db role to fix that issue but the other issue is that they cannot use the sp_changeobjectowner proc to change the schema to dbo. The thing that needs to happen is they need all the functionality of dbo without create or alter table. It 100% does not matter if they have the ability to grant themselves permission to create or alter tables because if they do they will be fired. We face a problem that newly hired developers are not following the development procedures and are creating or changing the DDL without letting the DBA group know so we can update the ER Studio models. I know security pretty well. It's quite simple but some things Microsoft does like not allowing deny on alter table is really silly. If anything I would think that Alter table would be tied to Create table but it's not.

So, I'm sure the answer is "You can't", but I'll ask again: Is there a way to give dbo access and deny alter table?

Thanks,
Daniel

Developers should have db_owner in the development environment. They should have unrestricted access in the user database that they are developing for. There just needs to be a communication line between the developers and the DBA about any schema changes. Where I work if they don't notify the DBA of a change, it doesn't get deployed to the other environments. And before it gets deployed, we do a code review on the schema changes.

Tara Kizer
aka tduggan

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-03-01 : 14:22:38

quote:
Originally posted by tkizer

quote:
Originally posted by SQLServerDBA_Dan

quote:
Originally posted by tkizer

Dbos can do anything in the database where they are dbo. There's really nothing you can do if they must dbo. I'm sure there is a way to not give them dbo and still give them all of the permissions that they require. Well unless you want them to create dbo objects like stored procedures. Is this for developers?

Tara Kizer
aka tduggan

Yes this is for developers. We want them to create SP's under the DBO schema and then grant themselves permissions to those SP's. I was going to give them the db_securityadmin db role to fix that issue but the other issue is that they cannot use the sp_changeobjectowner proc to change the schema to dbo. The thing that needs to happen is they need all the functionality of dbo without create or alter table. It 100% does not matter if they have the ability to grant themselves permission to create or alter tables because if they do they will be fired. We face a problem that newly hired developers are not following the development procedures and are creating or changing the DDL without letting the DBA group know so we can update the ER Studio models. I know security pretty well. It's quite simple but some things Microsoft does like not allowing deny on alter table is really silly. If anything I would think that Alter table would be tied to Create table but it's not.

So, I'm sure the answer is "You can't", but I'll ask again: Is there a way to give dbo access and deny alter table?

Thanks,
Daniel

Developers should have db_owner in the development environment. They should have unrestricted access in the user database that they are developing for. There just needs to be a communication line between the developers and the DBA about any schema changes. Where I work if they don't notify the DBA of a change, it doesn't get deployed to the other environments. And before it gets deployed, we do a code review on the schema changes.

Tara Kizer
aka tduggan

Yeah. That's the conclusion we came up with. The problem was that there is a "build launcher" program which takes all database and application changes from Vault and pushes them to the given environment on demand. Because of that the model is a bit out of sync. What we will need to do is cancel the DDL changes from being pushed via the "build launcher". The hope was that we could give the devs the db_owner role and Deny Create Table, so they would have to request DDL changes in the Dev environment too. If they purposely granted themself create table to get around it then that would have been a disciplinary issue not a security one. All that would have worked fine and dandy if Alter Table was also availible to Deny but its not.

Thanks for the comments guys. Have a good one...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources