locked
Adding Table into Migration now requires sysadmin role on SQLServer 2012 RRS feed

  • 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

    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 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