Answered by:
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
Monday, October 7, 2013 8:22 AM
Answers
-
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
- Edited by waynedrussell Tuesday, October 8, 2013 4:08 PM Credited name added
- Marked as answer by Fred Bao Wednesday, October 9, 2013 9:05 AM
Tuesday, October 8, 2013 3:51 PM
All replies
-
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.- Edited by Fred Bao Tuesday, October 8, 2013 2:07 AM Better
Tuesday, October 8, 2013 2:05 AM -
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
Tuesday, October 8, 2013 7:45 AM -
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
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.- Edited by Fred Bao Tuesday, October 8, 2013 8:57 AM Better
Tuesday, October 8, 2013 8:54 AM -
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
Tuesday, October 8, 2013 12:06 PM -
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
- Edited by waynedrussell Tuesday, October 8, 2013 4:08 PM Credited name added
- Marked as answer by Fred Bao Wednesday, October 9, 2013 9:05 AM
Tuesday, October 8, 2013 3:51 PM -
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.- Edited by Fred Bao Wednesday, October 9, 2013 9:05 AM Better
Wednesday, October 9, 2013 2:55 AM