locked
i just created a windows application and i am having problems with the database file permissions RRS feed

  • Question

  • i just created a windows application and i am having problems with the database file permissions

    after i have installed it with setup wizard i have to go to the program folder and to the database file and change the permissions for the users to full control, if i dont this the aplication wont work

    this is my connection string

    <connectionStrings><add name="landlord.Properties.Settings.dbConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" /> </connectionStrings>

    should i change the connection string ?

    or create the database programatilly ?

    thanks

    Pedro

     

    Friday, January 7, 2011 6:42 PM

Answers

  • Hello ,

    I suppose that you have created your database with Visual Studio Expess (VC# or VB).So you are using the user instance feature specific to SQL Server Express.It is a depreciated feature which i advice you to forget it ( i think that Denali will be the last version of SQL Server Express to support it ).A nice and useful feature which has been too often misunderstood and misused.

    If you want to know how to use user instance and to avoid its pitfalls, i suggest you to read

    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/824eb041-969f-4837-b8e9-e236d5d0a3c0

    or to visit this blog:

    http://blogs.msdn.com/b/sqlexpress/

    It was mainly managed by Mike Wachal who wrote many articles about user instances.Most of the articles about SQL Server Express 2005 are always usable ( this blog is the official blog of the team who develops SQL Server Express , so all the informations you will find in it are highlty valuable ). If i am not going wrong , you will find your answers to your questions in the posts from the 2006/2008 years.

    Have a nice day 

    PS : Sudeepta, when you are using user instance , you will have a database created for each user in the Documents and Settings folder for each user.I know there is no problem with XP SP3, but with Vista and Windows 7, it is another problem with the UAC ( maybe a hidden reason that pushed the SQL Server Express to give up user instance )

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    • Edited by Papy Normand Wednesday, January 12, 2011 2:08 PM postscriptum added
    • Marked as answer by pedrodinis Wednesday, January 12, 2011 5:07 PM
    Wednesday, January 12, 2011 2:01 PM

All replies

  • I suppose you are talking about database login & user permission. You need to add your login account to SQL Server instance and then assign permissions for the database.

    1. Connect to the SQL server express edition using the account, that you use for the installation ( if you are on 2008+, use the account you have added as administrator during installation)

    2. Open a new query window and execute the following query

    USE [MyDB]
    GO
    Create Login [DomainName\LoginName] FROM WINDOWS
    GO
    Create USER [DomainName\LoginName] FROM [DomainName\LoginName] 
    GO
    -- if you want the user to give "db_owner" permission
    EXEC sp_addsrvrolemember 'DomainName\LoginName', 'db_Owner'
    go
    
    Hope, this will fix your problem.


    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, January 7, 2011 7:31 PM
  • the ideia is to automate the instalation so the end user can have the application ready to run

    i have added the mdf file to the setup wizard

     

    but i does not work

    do you have any info how to do this

     

    thanks

    Wednesday, January 12, 2011 12:27 PM
  • Hello ,

    I suppose that you have created your database with Visual Studio Expess (VC# or VB).So you are using the user instance feature specific to SQL Server Express.It is a depreciated feature which i advice you to forget it ( i think that Denali will be the last version of SQL Server Express to support it ).A nice and useful feature which has been too often misunderstood and misused.

    If you want to know how to use user instance and to avoid its pitfalls, i suggest you to read

    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/824eb041-969f-4837-b8e9-e236d5d0a3c0

    or to visit this blog:

    http://blogs.msdn.com/b/sqlexpress/

    It was mainly managed by Mike Wachal who wrote many articles about user instances.Most of the articles about SQL Server Express 2005 are always usable ( this blog is the official blog of the team who develops SQL Server Express , so all the informations you will find in it are highlty valuable ). If i am not going wrong , you will find your answers to your questions in the posts from the 2006/2008 years.

    Have a nice day 

    PS : Sudeepta, when you are using user instance , you will have a database created for each user in the Documents and Settings folder for each user.I know there is no problem with XP SP3, but with Vista and Windows 7, it is another problem with the UAC ( maybe a hidden reason that pushed the SQL Server Express to give up user instance )

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    • Edited by Papy Normand Wednesday, January 12, 2011 2:08 PM postscriptum added
    • Marked as answer by pedrodinis Wednesday, January 12, 2011 5:07 PM
    Wednesday, January 12, 2011 2:01 PM