assign permissions without using dbo

Question

Post reply

assign permissions without using dbo

Buck-262603

Grasshopper

Points: 15
More actions
November 22, 2005 at 3:15 pm

#166652

I'm having a tough time with this one. I need to give a user "full control" over a database that he will use/maintain with the following tools.
enterprise manager
query analyzer
microsoft access data project
My problem is that I give him "dbo" access, when he creates objects, the objects are "owned" by him and cannot be used by other users.
the table names look like this.
domainName\userName.objectName
How do I assign him permissions to create, delete, and do pretty much anything he wants in the database but not have the objects he creates be "owned" by him.
Any help would be appreciated. Thanks...
Buck

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

When creating the tables, prefix them with

"dbo.".. EG, "create table dbo.myTable" rather than "create table myTable"

Lowell SSC Guru Points: 323518 More actions · Answer 2

I beleive you assigned the user in question the following roles:

db_ddladmin

db_datareader

db_datawriter

because of the ddl_admin role, the user can create objects for him to use himself, hence the objects are created as user.objectname.

if you assign the user the db_owner role in the database, then all objects he creates would be created under the sa/dbo instead; so new items become dbo.newobjectname instead of user.newobjectname; to avoid confusion, i would remove all other roles and only grant db_owner.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!