adding security to a linked table

Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!

Use a VIEW, actually 2 views. One for the hoi poloi, and the other for the chosen few. That is the 'best' option.

However, you can use column level permissions in the linked table if necessary. (Something about your question leads me to believe that you are using Access, in which case, my response may be totally 'full of beans'.)

|||I am using Access 2002 on the front end, sorry I thought I had mentioned that.|||Can someone please help explain how to go about doing this? I also need to modify field properties on the access end of a linked table. I applied the field property changes I wanted to the table in SQL server 2005, but am at a loss when changing them in access. I keep getting a message saying I can't save changes to a linked table. Is there a way for the changes made to the table in SQL to cascade down to the same table in access? Otherwise, how should I go about doing this? Thanks in advance!|||Can someone provide where to find more information about adding column level permissions in a linked table? I am using SQL server 2005 as a back end and Access 2002 as a front end. I added a new column to an existing table and now I want to add permissions to only this new column. Thank you much!|||

I still think that the best solution is to create an updatable VIEW (WITH VIEW_METADATA) that does not include the column in question. Those that should not access the column are provided permissions for the view, those that need access to the column are provided access to another VIEW that includes the column, or to the underlaying table.

For column level permissions, the specific Topic in Books Online is: GRANT Object Permissions (Transact-SQL). However, I recommend serious consideration of the VIEW option -it will be so much easier to maintain.

You might also find this thread useful. Or this thread.

adding security to a linked table

Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!

Use a VIEW, actually 2 views. One for the hoi poloi, and the other for the chosen few. That is the 'best' option.

However, you can use column level permissions in the linked table if necessary. (Something about your question leads me to believe that you are using Access, in which case, my response may be totally 'full of beans'.)

|||I am using Access 2002 on the front end, sorry I thought I had mentioned that.|||Can someone please help explain how to go about doing this? I also need to modify field properties on the access end of a linked table. I applied the field property changes I wanted to the table in SQL server 2005, but am at a loss when changing them in access. I keep getting a message saying I can't save changes to a linked table. Is there a way for the changes made to the table in SQL to cascade down to the same table in access? Otherwise, how should I go about doing this? Thanks in advance!|||Can someone provide where to find more information about adding column level permissions in a linked table? I am using SQL server 2005 as a back end and Access 2002 as a front end. I added a new column to an existing table and now I want to add permissions to only this new column. Thank you much!|||

I still think that the best solution is to create an updatable VIEW (WITH VIEW_METADATA) that does not include the column in question. Those that should not access the column are provided permissions for the view, those that need access to the column are provided access to another VIEW that includes the column, or to the underlaying table.

For column level permissions, the specific Topic in Books Online is:GRANT Object Permissions (Transact-SQL). However, I recommend serious consideration of the VIEW option -it will be so much easier to maintain.

You might also find this thread useful. Or this thread.

Adding Identity Column to BIG table

I've got a table with 36+ million rows. I've been asked to modify the
table and add in an identity column. The code I used caused SQL to
lock up and it maxed out the log files. :)

The code I used is:

Begin Transaction
Alter Table ODS_DAILY_SALES_POS
ADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)
Commit

Is there a way to break up the code? Maybe only do a few million
records at a time? Or is there a way to do this without locking
anything up?

Thanks,
Jennifer>> I've been asked to modify the table and add in an identity column.
The code I used caused SQL to lock up and it maxed out the log files.
<<

Do you have any idea why anyone would want to do this in the first
place? The idiot does not seem to understand that IDENTITY has no
meaning in a data model?|||[posted and mailed, please reply in news]

Jennifer (jennifer1970@.hotmail.com) writes:
> I've got a table with 36+ million rows. I've been asked to modify the
> table and add in an identity column. The code I used caused SQL to
> lock up and it maxed out the log files. :)
> The code I used is:
> Begin Transaction
> Alter Table ODS_DAILY_SALES_POS
> ADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)
> Commit
> Is there a way to break up the code? Maybe only do a few million
> records at a time? Or is there a way to do this without locking
> anything up?

The alternative is to rename the table and all its constraints,
create the table and new with constraints, triggers and indexes
and insert the data into that table. You can then do a loop which
takes a reasonable number of rows at a time. That requires, however,
that you somehow, can identify which rows you have copied and which
you have not. An advice is to perform the loop on the clustered of
the table. Once data has been copied, move referencing foreigh keys
to point to the new table, and then drop the old table.

The advantage of this approach is that the strain on the log is less,
particularly, if you permit yourself to switch to simple recovery
while you are running the move.

Note: above I said that you should recreate triggers and indexes. It
may be a good idea to do that after the copying is completed, but
just don't forget it. (You should package everything in a script
and first test in database where the table is smaller.)

A variation is to bulk out the data, and then bulk it in when the
table has no indexes. If you have bulk_logged recovery, this load will
be very fast. Personally, I prefer to create the clustered index first,
before I load, since building the index takes its time too.

Yet a variation is to use SELECT INTO (with which you can use
the IDENTITY function). SELECT INTO is also minimally logged when
you have bulk_logged recovery.

Finally, you have set your colunm to bigint. With 36 million rows, you
have a long way to go, before you 31 bits become too few for you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding an index to an exiting publication

I have an existing publication and I want to modify an index on my main server. I do not want the index to be pushed out to the other server, but I'm wondering if the index will break replication.

Any thoughts?

Try the link below for more information on SQL Server replication. Hope this helps.
http://www.replicationanswers.com/Default.asp

Adding a table to a publication

In testing (learning?) I added a table to a database after a publication was
created. Then I went to modify that publication but the new table didn't
show up in the list of articles. But when I did this with a view it did show
up. Is there something about new tables that prevents them from showing up
in the wizard? What if I use sp_addarticle?
Bob
Bob,
there's a checkbox in the publication properties, articles tab which allows
you to see unpublished articles - please can you check to see if it is this
that is preventing you from seeing the new table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yeah, that was checked. But I've since deleted the publication. Thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:epbCqnMrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Bob,
> there's a checkbox in the publication properties, articles tab which
> allows you to see unpublished articles - please can you check to see if it
> is this that is preventing you from seeing the new table.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>