>> And set my logged in user with the [Grant] Permissions and the [Select] Permissions on the

Just Issue Alter permissions on that particular table, so that it would be visible.

have you saved any changes you have made before trying to script out?
Are you sure you have the neccesary permissions to alter the table?

keep in mind also that when some changes are made, the table needs to actually be dropped and recreated
(for instance:
-adding a column anywhere other that at the end
-changing (e.g. renaming) a column
)
this usually happens invisibly without your knowledge... unless you go to "Tools->Options->Designers" and check the "Prevent saving changes that require table re-creation".
try doing this and see if management studio blocks you from doing that column change you're trying to do. if it does, then you know your account will need some more advanced permissions as well (like drop and create).

(you might have to use a more advanced login that already has drop & create, etc access on that table when you're trying to test the "Prevent saving changes that require table re-creation" option. if you have an SA account or similiar, try using that for the test)

@ wRatte

I do not have that option available?

SQL Server 2005 Enterprise.
Where would it be if it is not where it suppose to be?
(Image below)

@QPR
The Table is an older one, made about 3-4 weeks ago, it is not new.
>>neccesary permissions
I thought I did, how do I check for that?

@rrjegan17
>>Just issue ALTER
That is what I did prior to making the post, and it is still greyed out.
SQL-Server-2005-Designers.jpg

>> That is what I did prior to making the post, and it is still greyed out.

Oops...
Got it wrong earlier..
You can't create an ALTER script for a table.
You can ALTER a View, Function, Procedure and other objects but not a Table..
Sorry for the confusion earlier..

ah, sorry bout that. i'm using sql server management studio for sql server 2008. didn't know the option didn't exist in sql server 2005 yet. the "Prevent saving changes that require table re-creation" option would've appeared underneath the "Warn about tables affected" option.

but nevertheless, i don't remember being able to modify a column without that "re-creation" restriction blocking me. so i suggest experimenting by adding update, insert, etc access rights until your user gets the change without error. i can't think of anything other than that re-creation blocking you. not sure, but your user login might even need to have an advanced role. (like dbadmin - however that's be the most extreme case) only testing would tell.

(we're using sql 2000 and sql 2005 databases, i just like the sql 2008 management studio interface more due to the intellisense i get when writing queries on my test db's - sql server 2008 express is free and sufficient when only used as a query editor like i use it)

I read where you can do an Alter on a Field like to add in a longer string into the
Default Value and Binding.
By doing an Alter.

What I need to do is to add in a Default value that is a Message
To all new members on the site.
How can I add in this information into that Table.FieldName ???

Thanks
Carrzkiss

i just did a test on one of my own databases and the following change didn't require a recreate:

r-click on table -> edit
then i selected my column and filled in "This is a default note value" in the "Default Value or Binding" field of the column's properties (see screenshot). doing it with sql code as you mentioned now will also work yes :)

now when i add a new row (for example via r-click, "open table"), i leave the "Note" column's value as null - when i go to the new row (thus committing/saving the current row), the null changes to "This is a default note value"

so to do the above i reckon you'll need:
-alter
-insert
-select
-update (cause you'll probably want to update the existing rows with an update query to insert the new default value where they're already null - adding the default value now won't update the existing null values)

as far as i know if you add your user to the "db_ddladmin" database role, you should be able to do the above. your user's requirement to edit the table implies that it's needed as a kind of administrative user on that db.
Capture.jpg

@wRatte

I already know about setting the default value that way.
BUT if your are going to do multi-lines then that method does not work.
You have to do what I mentioned above (or) through the r-click.

I have all my Default Values already set and am ready to roll now.

Thanks again for your assistance.
All is good now, just would like to find out about doing it through the r-click instead of manually.
All though I have the code now and it all seems to be good and well.

Have a good one.
Carrzkiss

the r-click way i mentioned does "exactly" what your code does. in fact, if you generate a create script for that table after you do the r-click modification, you'll get the exact code you mentioned. just easier to remember than the scripting :)

"BUT if your are going to do multi-lines then that method does not work."
not sure what you mean here - i can do a mass insert and the default value will still get assigned.

wRatte.
>>"BUT if your are going to do multi-lines then that method does not work."

I am doing Multi-line, actually, it is more like a double paragraph that I needed as the default value.
(Instructions on how to use the site, that the user can [Delete] after learning)

>>not sure what you mean here - i can do a mass insert and the default value will still get assigned.

Not sure what you are talking about here myself?
I did not say anything about going "a mass insert and default value will still get assigned"???
Please explain

lol, ok. don't worry. we missed each other there. i thought you meant "multi line" as in inserting multiple rows at once into your table - with triggers for instance you need to cater for that via cursors, so i thought you thought the default constraint will need the same.

you got a point with the multiline thing. i would've resorted to using a script inserting/updating of the constraint also. (cause its not like you can specify it to put in a "char(13)+char(10)" - or at least not what i'm aware of). the value field for that default value in the gui takes text straight as you enter it, not in a formula kind of way.(as far as i know) :\