Adding Table into Migration now requires sysadmin role on SQLServer 2012

Question

Hi All,

I am using Entity Framework 5.0 and C#4.5 as part of a project. I have added a table as part of the continued development of the application. I now find that I have to additionally set sysadmin server role in SQL Server 2012:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [UserLogin]

This enables the changes to be reflected on the server used to run the application.

Does anybody know if this is correct behaviour when a table is to be added.

BTW - Adding fields to existing tables runs fine with sever role as public for the UserLogin.

Wayne Russell

Answer 1

Hello,

Welcome to this forum.

>>Does anybody know if this is correct behaviour when a table is to be added.

I think it is correct.

As far as I know, when we want to add a table to a database, we need to make sure that the login user has the permission for the database.

The sysadmin is on server level and it has all the permission for the database.

So if we change the login user to be sysadmin, we just expanded its authority.

If we do not want to give it so much right, we just need to give it the permission for the database we want to add a table into only.

If I have misunderstood, please let me know.

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi Fred,

Thank you for your reply.

Please could you expand "we just need to give it the permission for the database we want to add a table into only". What would be the best way to achieve this?

Wayne

Answer 3

Hi WayneRussell,

>>we just need to give it the permission for the database we want to add a table into only

I mean that we give the login account 'UserLogin' the permission to create and modify table for the database that we want to connect in entity framework.

For how to give the persimmon to the login account, there is a link regarding it:

http://technet.microsoft.com/en-us/library/ff878066.aspx

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

Hi Fred,

I currently have this script that sets up the database role and user login for the database:

CREATE LOGIN LoginUser 
		WITH PASSWORD = 'Password'
GO

Use myDatabase
CREATE USER user FOR LOGIN LoginUser
		WITH DEFAULT_SCHEMA=dbo
GO

Use myDatabase
CREATE ROLE userRole AUTHORIZATION dbo
GO
EXEC sp_addrolemember 'userRole', 'user'
GO

GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE, ALTER on SCHEMA::DBO to [userRole] 
GO

This is appears similar to information in the link you provided - am I missing something in my script?

Wayne

Answer 5

Hi All,

I have found a solution by running the following script:

ALTER ROLE [db_owner] ADD MEMBER [user]

Credit to Stuart Wells at  www.sadler-wells.co.uk for supplying the solution.

Wayne Russell

Answer 6

Hi WayneRussell,

Glad that you have found a solution better way to achieve this and share the solution for us.

As far as I know, the codes below is actually giving the login user the permission for the databse we will to use in entity framework.

ALTER ROLE [db_owner] ADD MEMBER [user]

Thanks

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.