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.

Making use of other fixed server roles other than sysadmin

Are there a lot of folks out there that make use of other fixed server roles
other than sysadmin. ? Never really seen a strong use for ever using themI've seen use of both the securityadmin and processadmin
roles, although rarely. I tend to control all access to
the server as either developer needs (DDL level
security), enduser needs (DML level security) or DBA
needs (sysadmin).
It looks like Yukon is going to include more fine-grained
permission assignment, so I'd imagine this would be an
area to revisit once it is released -- and no, I have no
idea when :)
My 2 cents, hope it helps.
>--Original Message--
>Are there a lot of folks out there that make use of
other fixed server roles
>other than sysadmin. ? Never really seen a strong use
for ever using them
>
>.
>|||I agree with James... Most small shops have a single or two dbas, and
everybody does everything... But in a large company ( I work in an area
where there are some major banks), there may be 30-50 dbas. There is so much
work that each dba is assigned a specific task. They then use the other
server roles to give the minimum permissions...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OKHLc6OzDHA.2156@.TK2MSFTNGP09.phx.gbl...
> Are there a lot of folks out there that make use of other fixed server
roles
> other than sysadmin. ? Never really seen a strong use for ever using them
>|||On Sat, 27 Dec 2003 18:59:27 -0800, "Hassan" wrote:
>Are there a lot of folks out there that make use of other fixed server roles
>other than sysadmin. ? Never really seen a strong use for ever using them
Where the application is going to be maintained by people who should not
typically have sysadmin access, but need to be able to add logins, you
can grant these people securityadmin, and then also db_securityadmin in
the application's associated database(s).
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costellosql

Making a report smaller...?

Alright. I'm stuck. I admit it!

I have a bunch of names, and each name can have one or more 'roles'(operator, reader, key operator, etc. Just random words really.) attached to it.

Using reporting services, I've managed to get the information I need with relative ease... the only problem is, with 900 some records to display, it's current length of 41 pages with just one column going down the left side of each page is not exactly preferred by my superior (can't say I blame him really. Looks kind of odd!)

It looks like this right now:

Name1

Function

Function

Function

Name2

Function

Function

Name3

Function

Function

etc all the way down to page 41

I need it to look something like this:

Name 1 Name 4 Name 7

Function Function Function

Name 2 Name 5 Function

Function Function Name 8

Function Function Function

Name 3 Name 6 Function

Function Function Function

etc. Or some variation of...

I've fiddled around, and merely adding one extra column to the initial table-layout with the same =(!UserName etc) just merely replicates the data in the second column... not giving me the new stuff.

I'm quite new to reporting services, but none of the tutorials I've seen/done seem to accomodate for this... Heeelp!

How about this:

Select the first third of the data as a field for your dataset (you'll need some index field in your data to indicate how much data you've selected). Call it say, Column1.

Select the second and third part of the data similarly as respective fields accordingly. Call these Column 2 and Column 3.

Then set up a table or matrix with fields for Column 1 2 and 3.

|||

You can put your table into multiple columns on the page by:

From the main menu, select report|report properties

select Layout tab

Change the columns to 3

type 0in for spacing and .5in for the margins

check it out in preview

these instuctions are from the book SSRS2005 by Brian Larson, get your own copy from Amazon

|||

Awesome. That does the job right there.

Thanks for the help! Both of you