locked
Visual Studio SQL Project: Modify the Create Script RRS feed

  • Question

  • Hello,

    I'm using Visual Studio to develop a large set of tables for my application's database. I am in a situation, however, where I do not have permissions to create a new database on my data engine. Once I log in with my SQL credentials, I can create tables in a specified database, but I am not allowed to edit other databases or create new ones. This creates a problem for me when I try to use the Create scripts that MSVS produces for a SQL Project. These scripts check if the database exists yet and, if it does, it drops the existent database and then re-creates it. As you may glean, the re-creation doesn't work in my circumstance, but the drop does. This means I end up unintentionally dropping my database every time I try to create it.

    I would love to remove this DROP and CREATE from the script. I just want the Create script to make all my tables, insert table rows, etc. I would love for the Create Script to leave the Database itself alone. I've looked through the Project settings but I'm not seeing such an option. Does this exist? How can I ensure that I don't drop my database in the future?

    Thanks!

    -Aws



    • Moved by Tina-Shi Monday, June 8, 2015 5:49 AM the issue is related to the sql
    Friday, June 5, 2015 5:32 PM

All replies

  • Hi Aws,

    Thanks for your posting in MSDN forum.

    Since this issue is related to the SQL credentials, so we will move this case to the SQL Security forum, you will get better support.

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

    Monday, June 8, 2015 5:49 AM
  • I think what you're asking on is about the Always re-create database setting. Set it to No and database will not get dropped everytime ie script will not include the drop database statements

    https://msdn.microsoft.com/en-us/library/aa833291%28v=vs.100%29.aspx


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, June 8, 2015 6:16 AM
  • Hello,

    I am in a situation, however, where I do not have permissions to create a new database on my data engine. Once I log in with my SQL credentials, I can create tables in a specified database, but I am not allowed to edit other databases or create new ones.



    Hi Aws,

    Besides other post, in SQL Server, to create a new database, your account requires CREATE DATABASE permission in the master database, or requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission.  Please ask your DBA to grant the permission for you.

    Also, to edit other databases, we need to add database users to the these databases and assign appropriate statement and object permissions to the users.

    To give your user all read permissions, execute the following script :

    EXEC sp_addrolemember N'db_datareader', N'your-user-name'

    That adds the default db_datareader role (read permission on all tables) to that user.

    There's also a db_datawriter role - which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:

    EXEC sp_addrolemember N'db_datawriter', N'your-user-name'

    Thanks,
    Lydia Zhang



    Lydia Zhang
    TechNet Community Support


    Monday, June 8, 2015 7:21 AM
  • I am looking for the "Always Re-Create Database" option, Visakh! Thank you. 

    It appears as though this option isn't working quite right for me, though... As you can see in the image below, I seem to have the Re-Create Database option disabled. Please note that I'm using Visual Studio 2013. Maybe other folks' UI looks different...

    http://synapsing.com/SQL-Project-Options.png -- (Sorry for the Poor-Man's Link... Apparently my account hasn't been verified?)

    When I build my solution, however, the script gets generated with the following SQL snippet at the top of the script:

    ...
    
    IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE [$(DatabaseName)];
    END
    
    GO
    PRINT N'Creating $(DatabaseName)...'
    GO
    CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS
    GO
    USE [$(DatabaseName)];
    
    ...

    Am I missing something simple, here? I feel like my build is not heeding its own options. Perhaps I'm making a mistake when selecting my configuration? I've tried building the Solution in both Release and Debug configuration, but I get the same results either way. I notice that in Project Debug Settings, the Configuration is "N/A". Shouldn't this read "Release" or "Debug"? I cannot select a Configuration to specify the settings for...


    • Edited by Aws505 Monday, June 8, 2015 4:00 PM
    Monday, June 8, 2015 4:00 PM