June 20, 2002 at 8:07 am
As DBO, can I give permission to a user to
CREATE TABLE, where the table created is owned by a different user?
June 20, 2002 at 8:22 am
Only if you make the user a member of the db_ddladmin database role.
Then they could run the following statement:
create table user2.tablename (pkey int)
DanW
June 20, 2002 at 8:47 am
Almost. From BOL:
Permissions
CREATE TABLE permission defaults to the members of the db_owner and db_ddladmin fixed database roles. Members of the db_owner fixed database role and members of the sysadmin fixed server role can transfer CREATE TABLE permission to other users.
Right click on the db and select properties, permissions to grant these in EM.
Andy
June 20, 2002 at 9:43 am
Sorry, but I don't care what BOL says.
I don't understand why everyone on this site always quotes it?
What I wrote is 100% accurate. If you want to 'change' an existing object to a different users permissions within the database then you need to grant the user db_securityadmin permission in the database. db_ddladmin is enough to 'create' an object owned by someone else. Try it out.
I agree that if you want to do this system wide then you could use the sysadmin role, but you would be crazy to grant someone sysadmin just so they can create and transfer ownership of tables. Same goes for DBO on the database level.
DanW
June 20, 2002 at 9:46 am
What is really funny is why you grant "CREATE TABLE" to someone different when the table already exists. 🙂 There isn't anything you can do with create table if the table is there. I assume either the owner needs to be changed (BAD BAD idea) or they want someone else to run ALTER table.
Also, db_owner isn't sysadmin. I could see granting someone db_owner role to develop in a db (not production) so all objects are dbo owned.
Steve Jones
June 20, 2002 at 9:56 am
I agree,
It's probably best to keep things simple and have all objects owned by dbo. Sometimes we are forced to make things more complex than they need to be.
DanW
June 20, 2002 at 10:09 am
Cant speak for the rest, but I often quote BOL because 9 times out of 10 the person asking just hasn't known exactly where to look in BOL and it has the answer as good as anything I could type. The other 1 time out of 10 they didnt bother to look - I dont mind helping anyone with a question, but it doesn't hurt to point out that BOL should always be the FIRST place to look.
Perhaps I misunderstand the original question though. I took the question to mean that they wanted to let the user create their "own" tables themselves. Create table solves that nicely without granting excess permissions. If its a matter of then changing the owner (I agree with Steve that dbo is the way to go) then you're limited to db_ddladmin and db_securityadmin (plus sa and dbo of course) for executing it.
Just because Im the curious sort, why does quoting BOL bother you?
Andy
June 20, 2002 at 10:27 am
I don't feel like it adds much value to the site.
When someone asks a question on this site I would hope that they are looking for the best practices way of doing things, not what is suggested by some technical writers at Microsoft. We are in the field dealing with the software on a daily basis and can add much more value to any question than BOL can. I agree they should know that they can find answers in BOL, but if you keep pointing that out then why would they come back to your site?
DanW
June 20, 2002 at 10:55 am
I think for the reasons I mentioned earlier. Sometimes it's hard to find what you're looking for in BOL, especially if you're new to SQL and don't know what everything is called. A rare few too lazy or just don't know to look there first.
I teach the developers I work with to try BOL, then MSDN, then groups.google.com, THEN come ask me for help or try a site like this one that specializes in whatever subject they are working on. Pretty much the same methodology I use myself. Not a novel approach, but sound and teaches them to fish.
I try to look at the skill level of the person asking the question and determine if a BOL quote is appropriate. If you scan my 2600 or so posts you won't see many. I'll also add a comment anytime I think a key point is being missed or an alternate solution hasnt been presented. I also try to provide background that I think a less experienced user might find helpful.
Finally, I think newer users return here because we try to answer every question with a calm, honest approach to solving it. Most users are thrilled to get an answer at all, much less the several points of view we can usually offer as a community. More experienced users I think visit here because it's a chance to share their knowledge and to participate with peers in a professional environment. Ever notice there are NO non-SQL related ads on the site?
Keep in mind I don't disagree entirely. I don't want to see every question answered with a 3 page long quote from BOL. That's excessive.
Finally, we can agree to disagree on this. We each contribute in our own way and hopefully to the betterment of the user community. I hope that even with the miscellaneous BOL posts that you'll continue to participate and offer the other readers the benefit of your experience.
Andy
June 20, 2002 at 10:58 am
Tend to agree with Andy. BOL, while not always accurate, does provide a nice reference point for someone to check in addition to the post. I just wish we could easily post a BOL link to a local BOL page. Maybe MS would number pages.
Steve Jones
June 20, 2002 at 11:59 am
Thanks for the follow up guys.
I do really enjoy your site, especially since thier isn't alot of non SQL content. I think I'm just in a bad mood today and feeling particularly argumentative. Your "Almost." comment ticked me off for some reason... maybe I'm P.M.S.ing 🙂
I'm sure Bryan99y is pretty baffled by all this... he just asked a question.
DanW
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply