Table create permissions on SQL Server 2005

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
Morpheus7Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Try..

USE YourDBName;  
GRANT CREATE TABLE TO Pawan;  
GO

USE YourDBName;  
GRANT ALTER TABLE TO Pawan;  
GO


Note - Pawan is your user name
0
 
Morpheus7Author Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Pawan KumarDatabase ExpertCommented:
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
 
Morpheus7Author Commented:
Hi,
I am sa on the server. I have asked the user to disconnect and reconnect but the error persists.

Thanks
0
 
Pawan KumarDatabase ExpertCommented:
Can you please provide me the screen shot the current access setting he has ?
0
 
Morpheus7Author Commented:
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
 
Morpheus7Author Commented:
That's great, many thanks.
0
 
Pawan KumarDatabase ExpertCommented:
Welcome Morpheus !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.