create login alias DBO

Hi:

When I set up databases for my users, I create them a user and alias it as the DBO. They can then use this user in their code.

The problem is that if a user is aliased as a DBO, they can change the size of their database, create jobs, backup their database, set up replication (from their own sql server), etc...

Is there a different way to create their login (with minimal administrative effort) to give them access to create objects in their database, without allowing them access to these other tasks?

Thanksyou can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).|||Originally posted by jora
you can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).

And all objects will belong to this user: like tom.newtable. It needs to be careful - there is possibility to have couple tables with the same name but with different owners.

BOL:For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.|||I agree about the owner of the objects. As I understood the mail though (and I could be wrong offcourse) tommy only used the dbo user so the login was able to create objects. Thefore I see no problem, the user can still use his login in his/her applications.|||Thanks to all of you for your input. I love this forum, everyone is so helpful!

Ok, moving forward, may be able to use that type of login. However, I can see where it would mess up object owners.

For now, I will start by denying users to certain functions I want to lock out. For example, DENY BACKUP loginname.

Does anyone know how to deny a user from creating a job? I can not find it in BOL.|||I just remove the guest user from msdb, myself. So long as no one needs to save DTS packages there, it should work out ok.|||MCrowley, you rock! Great idea, I actually have denied access to create DTS packages on the server. One question, however, if I remove the quest user from the MSDB, will users still be able to DTS from their OWN machine? For example, they schedule a package on their own machine that pulls down updates, or whatever. Also exporting tables from their own database?

I'm going to test now.

Thanks!|||I removed the Guest login from the MSDB database, and the user (aliased as DBO of their own database), and still change the size of their database, log, and can still backup their database. It throws errors, that it can't write to the MSDB database, and the user is not a "user" in the MSDB, but it still works. It does, however, disallow them from creating jobs on the server!

Any other ideas on how to deny users changing the size of their database and backing it up? I tried:
USE DBNAME
GO
DENY BACKUP DATABASE TO LoginName
But since the login is aliased as a DBO, I get the error:
There is no such user or group LoginName.|||I just ran a test. You can take the users out of the db_owner role and put them in the db_ddladmin and db_securityadmin roles. You can even create your own special "db_owner" role that includes these for ease of administration. This will grant them the ability to create tables as dbo, but as Snail pointed out they will more than likely create tables in their own schemas only. The developers are going to have to be trained to specify the owner while creating objects. As this is possible when you add the user to the db_owner role, anyway, there should be only a few problems to start that gradually go away.
Hope this helps you in your problem.|||MCrowley, thanks for the info, I will experiement with that moving forward. I do have a bunch of databases that are already in place, and I can't make a drastic change like that right now.

Remember that these users are aliased as the DBO, they are not the DBO, sa is the DBO. I need to figure out how to stop users from backing up, restoring, and changing the size of their db.

Thanks|||In the MSDB I denied the public role access to the sp_add_job, and this made it so they can not add jobs to the server! Great!

Now, I just have to figure out how to prevent them from backing up and changing the size (or recovery model) or their database.|||Preventing them from changing the recovery model should be easy enough.
revoke exec on sp_dboption from public

This may have some uninteded consequences, as Enterprise Manager most likely uses this procedure to display information as well as change it. If your users are using EM for all their DBAdmin needs, then they will be getting all sorts of errors when the look for database properties. This may not be a problem for you,a s you are trying to restrict this stuff, anyway.

In order to prevent the backing up of databases/transaction logs, you are going to have to take these guys out of db_owner. I do not know of any other way of restricting that functionality (which, naturally does not mean a way doesn't exist...).