Solved

Table create permissions on SQL Server 2005

Posted on 2016-11-30
9
50 Views
Last Modified: 2016-12-01
Hi,

How would I grant create and alter table to a role in SQL Server 2005?  The development team wish to use the Entity Framework in their application and this requires that role has the create and alter table permissions. Any guidance would be appreciated.

Thanks
0
Comment
Question by:Morpheus7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41907048
Try..

USE YourDBName;  
GRANT CREATE TABLE TO Pawan;  
GO

USE YourDBName;  
GRANT ALTER TABLE TO Pawan;  
GO


Note - Pawan is your user name
0
 

Author Comment

by:Morpheus7
ID: 41907142
Hi,

Many thanks for the response. I have given the role create table permissions, but they are still unable to create a table. I am unable to apply the alter script.

Thanks
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41907193
This means you do not have access to perform  these operations.

Are you the admin on the SQL Server. Also ask the person to disconnect SSMS and connect again.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Morpheus7
ID: 41907413
Hi,
I am sa on the server. I have asked the user to disconnect and reconnect but the error persists.

Thanks
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41908323
Can you please provide me the screen shot the current access setting he has ?
0
 

Author Comment

by:Morpheus7
ID: 41908355
Hi,

One thing I have realises is that role that requires access does not have a schema associated with it. Should it have one?

Thanks
0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41908361
Try this..

GRANT ALTER ON SCHEMA::dbo TO UserName

CREATE ROLE PawanPawan
GO

GRANT CREATE TABLE TO PawanPawan
GO

GRANT ALTER ON SCHEMA::dbo TO PawanPawan
GO 

EXEC sp_addrolemember 'PawanPawan', UserName
GO

Open in new window


https://msdn.microsoft.com/en-us/library/ms187940.aspx
http://www.sqlservercentral.com/Forums/Topic1107353-359-1.aspx
0
 

Author Closing Comment

by:Morpheus7
ID: 41908768
That's great, many thanks.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41909648
Welcome Morpheus !!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question