February 6, 2003 at 11:34 am
I am in the process of creating SQL Server roles for our many databases (SQL2k). These servers are used by several web developers, data analysts, and a few power users.
Is there another way to allow users within a role to create tables/objects that will default to DBO as the owner instead of their username without granting them db_owner/sysadmin rights?
37SOLUTIONS
We'll find the one for you!
Website Hosting | Website Design | MSSQL/MySQL Hosting | Database Development | Research/Consulting
February 6, 2003 at 12:35 pm
I don't see it is possible. They have to be member of db_owner at least.
February 7, 2003 at 1:33 am
You may be able to grant them the db_ddladmin
role.
From BOL
db_ddladmin 'Adds, modifies, or drops objects in the database (runs all DDLs).'.
They may still have to prefix there objects with dbo.
Steven
February 13, 2003 at 6:00 pm
Hi there,
I think the only way to do this is save to save the table with the dbo prefix.
We've had this problem here.
One of the developers here keeps forgetting to save it using dbo as the prefix and I end up having to change the owner on it afterwards.
Mark
February 13, 2003 at 7:24 pm
They have to prefix it. By default only the database owner (who maps to dbo), those who are aliased to dbo, and sysadmins (who map to dbo) automatically create objects with dbo as an owner. Even if UserA is a member of db_owner, the object is still created as owned by UserA of no ownership prefix is specified.
BTW, Steven is right, db_ddladmin role members can create objects owned by dbo. They just can't assign permissions to those objects after they are created. Also, when you change the object owner (this requires membership in both db_ddladmin and db_securityadmin), all permissions are automatically revoked. So if you want a user to also be able to assign permissions, you'll have to give that user membership in the db_securityadmin role.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 16, 2003 at 6:46 pm
Thanks everyone for your responses!
37SOLUTIONS
We'll find the one for you!
Website Hosting | Website Design | MSSQL/MySQL Hosting | Database Development | Research/Consulting
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply