Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create table permission denied Expand / Collapse
Author
Message
Posted Wednesday, November 25, 2009 4:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 6, 2010 2:30 AM
Points: 23, Visits: 117
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
Post #824996
Posted Wednesday, November 25, 2009 10:57 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #825055
Posted Thursday, November 26, 2009 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 6, 2010 2:30 AM
Points: 23, Visits: 117
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
Post #825353
Posted Thursday, November 26, 2009 2:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #825363
Posted Tuesday, January 12, 2010 10:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:51 AM
Points: 13, Visits: 3,161
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.
Post #846355
Posted Tuesday, January 12, 2010 11:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #846389
Posted Tuesday, January 12, 2010 11:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:51 AM
Points: 13, Visits: 3,161
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.
Post #846404
Posted Wednesday, January 13, 2010 12:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #846716
Posted Wednesday, January 13, 2010 3:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:51 AM
Points: 13, Visits: 3,161
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.

Post #846782
Posted Wednesday, January 13, 2010 5:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #846804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse