Create table permission denied

Question

Create table permission denied

cute_lhen05

SSC Eights!

Points: 989
More actions
November 25, 2009 at 4:04 pm

#228178

Good day everyone,
Iam trying to create roles in the database; what permission should i give in order for the user to allow to create tables in the database without necesarily adding them in as dbo?
thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Make the person a member of ddl_admin, or grant them CREATE TABLE rights in the database and ALTER rights on the schema.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cute_lhen05 SSC Eights! Points: 989 More actions · Answer 2

Thanks for your help! it worked! however there's a message showing everytime the user creates a table like

"you are not logged on as the databae owner or system administrator. You might not able to save the changes to tables that you dont own"

After i click ok, i can still enter the fields/columns for the table then everytime i click on the save icon another warning message appears like

"warnings were encountered during the pre-save validation process and might result in a failure during save. Do you want to continue attempting save?

After i clicked "yes"; the table did saved, however will there be any errors in the tables due to the warning statements i received earlier? why are these statement showing?

Thanks,

Aileen

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

cute_lhen05 (11/26/2009)
Thanks for your help! it worked! however there's a message showing everytime the user creates a table like
"you are not logged on as the databae owner or system administrator. You might not able to save the changes to tables that you dont own"

Simple fix for that. Don't use the GUI table designer.

Isn't there an option to see the warnings generated?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RunnerIE Say Hey Kid Points: 665 More actions · Answer 4

This might be releated to the same original problem...

I have a stored procedure that runs fine under my (dbo) account, but gives errors from basic users where I have a developmental db that I am now trying to secure properly for wider useage.... if I move the temp table to tempdb do I avoid the error messages and the need to add the user rights as you have outlined in the earlier posts?

Thanks, Brian.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Depends. Permanent table in TempDB or #table?

Do note that creating permanent tables in TempDB is a bad idea and has a number of logistical problems since TempDB is recreated from scratch when SQL starts

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RunnerIE Say Hey Kid Points: 665 More actions · Answer 6

Are the only logistial issues that I might try to drop a table that doesn't exist after SQL is (re-)started... ?

Is a good workaround to use the IF OBJECT_ID(....) IS NOT NULL to avoid problems with the table disappearing on me? ie..

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL

DROP TABLE #temptable;

GO

I am aware of the #tmptable (i.e. local) vs. ##tmptable (i.e. global table) naming issue but thanks for picking that up just in case.

Thanks again,

Brian.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

bbryce (1/12/2010)
Are the only logistial issues that I might try to drop a table that doesn't exist after SQL is (re-)started... ?

No the issue is, if it's a permanent table (not a # table), you might try using it after SQL restarts and it won't be there, neither will the permissions to create and drop.

So, are you thinking about a temp table (CREATE TABLE #temp or CREATE TABLE ##temp) or a permanent table in TempDB (Create Table TempDB.dbo.MyTable)?

If you're thinking about a #table, then there are no concerns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RunnerIE Say Hey Kid Points: 665 More actions · Answer 8

It is a temp table I going with so will use #tmptablename (or ##tmptablename) so I'm 'good-to-go'. Thanks for the help on this one, its truely appreciated.

Now - can you just do something about the icy cold weather we are getting in Ireland? Just kidding. I saw on one of your other posts that you are in South Africa... which I'll bet isn't having the same problems right now 🙂

Cheers,

Brian.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

bbryce (1/13/2010)
Now - can you just do something about the icy cold weather we are getting in Ireland?

Afraid not. I'm still trying to get the weather to confirm to my will. It's proving strangely challenging. 😀 :hehe:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass