Manage detached mdf file

I want to manage my sql express .mdf database file (e.g. set Roles) but I seem to only be working with a temporary instance of my database when I attach to .sqlexpress and manage in SQL Server Management Studio Express (SSMSE). The Role I set is gone when I detach and re-attach.

If I try to manage my file in Visual Studio under Server Explorer, I do not see the "Roles" folder to modify roles.

Any help is appreciated.

Roles don't disappear once they've been created as they are part of the database where they are created. If you're trying to create Server Roles, those obviously stay with the Server instance, not with the database. This is true even for temporary instances. There are a couple things that are probably causing confusion.

If you created your database in VS, you are likely working with a User Instance. This is a temporary instance that is create at runtime of your application. You can manage a User Instance similar to any other Instance of SQL Server, but you can only connect to it using Named Pipes. You will have to query sys.db_os_child_instances (see BOL for more info on this) to determine what the pipe name is for the User Instances on your computer. You should also be aware that each User has a separate and independent User Instance; this is important if you're talking about Server Roles, which would have to be created on every User Instance separately after the User Instance is created the first time it is used. Database Roles will travel with your database, so those are a bit easier to manage.

You may also be confused by the fact that VS actually creates several copies of your database as part of the Build and Debug process. It is possible that you are creating the Roles in one copy, but then looking for them in another copy. Check out the FAQ topic on why data doesn't appear to be saved in VS for an explaination of what's happening with the copies of the database.

If you give us an idea of what types of Roles you're looking to manage, we'll have a better idea what to suggest.

Mike

|||

Ok, I'll take a look at the information here. The type of role I am trying to create is Application Role. In order to use Full-text index on my database I cannot use User Instances. Therefore, I need to have an Application Role for the the application to login.

Yes, I created the database in VS.

Ok, I've resolved this by creating the database in sql express. You were right about the different copies of the mdf file. I guess if I don't want to use User Instances (which I don't) then I need to create the db in sql express and update my connection string to use either windows authentication or sql authentication.

I am having login issues however...will create another post for that.

Thank you.