7

EDIT I found the solution. Details at this question: How to use a different connection string (but same database) for migrations

I have created a SQL Server login for use by my website:

USE Master
CREATE LOGIN website_live 
    WITH PASSWORD = N'ItsASecret', 
    DEFAULT_DATABASE = MyDb, 
    DEFAULT_LANGUAGE = British, 
    CHECK_EXPIRATION = OFF, 
    CHECK_POLICY = OFF
GO

Then I gave it read and write permissions:

USE MyDb
CREATE USER website_live 
FOR LOGIN website_live 
    WITH DEFAULT_SCHEMA = dbo

EXEC sp_addrolemember N'db_datareader', N'website_live'
EXEC sp_addrolemember N'db_datawriter', N'website_live'

And used that login in my connection string

Everything works until I publish from Visual Studio with a database migration that requires a schema change (Edit: by this I mean a change to the structure of the database. I don't mean that I want to change the schema that the website is using).

I don't really want to give the website permanent permissions to alter the database. Can I create another database user that is only used during migrations? Would that be secure? How do I do that and can I keep the convenience of publishing with just a couple of clicks?

NB I'm using Entity Framework migrations. When I publish the website from Visual Studio, the website is automatically opened in a browser then, if there are database migrations to run, these are run using the website's credentials.

Community
  • 1
  • 1
Colin
  • 22,328
  • 17
  • 103
  • 197
  • Do you mind elaborating on "requires a schema change"? Looking at the extensive permissions you have given `website_live` it would seem to me that you are not making use of schemas at all. Just asking because I have implemented myself a DB where I am not making use of schemas or roles at all but rather rely on a `Users` table with permissions stored in it. Would that be an option? – Ralph May 16 '16 at 13:32
  • @Ralph A schema change is when we add, alter or remove a table or a column or a view (etc). If I add the website to either the `db_ddladmin` or `db_owner` roles, then my publish process is effectively one click because EF can then run the migrations, but I'm not keen on giving the website those permissions on a full-time basis – Colin May 16 '16 at 14:08
  • I am not sure if you are looking for this, but this will prevent alter of your datatable: http://stackoverflow.com/questions/18863512/how-to-prevent-dbcontext-from-altering-the-database – Ankush Madankar May 17 '16 at 09:01
  • @AnkushMadankar I want to alter the database during migrations, but prevent schema changes at other times. Currently I am manually adding the user to `db_ddladmin` before I publish, then removing them again after publishing. I'd like to automate that process. – Colin May 24 '16 at 09:05
  • If you found a solution, please write an answer, so this question isn't "unanswered" any more. – TToni Jun 24 '16 at 09:47

0 Answers0