Diagram, script and security ...

Three things:

1 - I attached a database from another computer in the Microsoft SQL Server Management Studio Express tool. It looks like my database diagrams did not follow.At first, it told me that the owner of the database was not set correctly and, hence, diagrams could not be supported. I ran the following query to fix that -> Alter authorization on database::DB_NAME to [MACHINE_NAME\USER]. Now, I can create new diagrams but I would like to see the ones I had before. How can I transfer my database files (.mdf and .ldf) with the diagrams?

2 – I would like to generate a database script that would not only create the table structure but also generate the insert statements to populate the database. When I use the “generate scripts” wizard, I don’t see an option where I could get the insert statements included in the script.Any help would be appreciated.

3 - I would like to read/find a short tutorial on how to set up security for my database. For now, I use the default user/security everywhere and I am sure it is not a good way of doing things. Basically, I would like to make sure I am not leaving a big security hole without having to read an entire book on SQL Server.Where could I get started?

Thanks a lot,

Dom.

1/ the diagrams should have come across with the database as they are stored in special tables, was this a SQL 2000 db that was upgraded by any chance?

2/ There is no way to generate insert statements in the UI today, there are some samples out in the community that can do it through.

3/ Have you read books on line?

|||

1 - No this is not a database upgrade, a SQL Server 2005 Express Edition to SQL Server 2005 Express Edition

2 - This is sad, there should be something to generate insert statements.

3 - Do you have any good ones to suggest?

thanks.

|||

Check out Securing your SQL Server Express Edition Server, it's from beta, but all the prinicples apply. You can also read the various pages of the BOL linked to from the Security Consideration for SQL Server topic.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Device activation error

Hi All,

Currently I have a PC that has MSDE installed on it and is attached to database (MyData.MDF and a log file MYLog.LDF) located on its hard drive at c:\data\.When I detach from the database, place a copy of the two files noted above on my networkdrive @. u:\data and try to attach I get the following error’s:

SQL[1] exec error = -1: Changed database context to 'master'.

“Device activation error. The physical file name u:\data\MyData.MDF may be incorrect.”

I have done some testing a have found that I can attach to a copy of my database if I move it anywhere on the c: drive, and or even to a 1Gb USB key attached to the system(e:\).So far it seems to only be an issue if I move it to a mapped network drive.If anyone could please provide me with any info it would be greatly appreciated.

Thanks.

James D.

First, your data must be stored on an approved network server/storage you want to be supported. Second, you will have to use a special trace flag to force sqlserver to mount a networked db file. See the following article:
http://support.microsoft.com/kb/304261|||

It's because your U: drive isn't a local drive. By default, it's not support to place data files on mapped shares or UNC paths. You can find more information in the following article:

Description of support for network database files in SQL Server

http://support.microsoft.com/?id=304261

-Sue

|||

Excellent, thank you for the response.

After I use the trace flag mentioned and get my MS SQL Server Desktop Engine to mount to the networked db files, will it be possible to have a second PC with MS SQL Server Desktop Engine also installed on it to connect to the same networked DB files at the same time as the first unit, or will it be limited to just one PC at a time due to file locks? We require that at least two units(possibly up to 3 more in the future) can all share, write, and read to same networked db files at the same time. If you could please let me know when you have a moment it would be great.

Thanks.

James D.

|||

Sqlserver is a shared nothing technology. I.e. only one instance can access the data at any time. So, no, you cannot have another server hits that network database file(s). As it stands, unless your hardware is part of the qualified HCL, you're running in an unsupported platform.

Btw, sqlserver has a feature called Scalable Shared Database (SSD) which allows multiple instances to share the same database file. However, this requires a SAN.

Determining Trigger Order

I'm not sure if this is the correct forum or not for this question. Basically, i have 6 triggers attached to my table (2 of each type). I used sp_settriggerorder to make sure certian triggers fire first.

Is there any way to go back and determine what order a trigger has been set to (first, none or last)? I've scanned through all of the system tables and don't see anything.

Thanks in advance.

With some digging, you can find that they encode the order into the status fields on sysobjects. However, the way they are exposed is with the ObjectProperty() function -- it takes parameters such as ExecIsFirstUpdateTrigger.

It is not obvious to me how triggers are exposed from the Information_Schema views.

The digging involves using sp_helptext on the sp_settriggerorder and sp_helptrigger stored procedures.

|||That's exactly what I was looking for.

I didn't even think abou tusing sp_helptext to see what the procedure did. I'll definitely use that next time I want to know what SQL is doing.

Thanks!