Altering linked tables

I have a table (in Access) that is linked to the SQL server, and I
need to add a column to it. I wrote the following:

ALTER TABLE Census
ADD COLUMN 'ActiveFacility' BIT;

and I get a syntax error that I do not know how to solve. The column
needs to contain yes/no data for each record.

Any help for a struggling newbie?
Thanks,

ChristineChristine (cbrandel@.rehabmanagement.com) writes:
> I have a table (in Access) that is linked to the SQL server, and I
> need to add a column to it. I wrote the following:
> ALTER TABLE Census
> ADD COLUMN 'ActiveFacility' BIT;
> and I get a syntax error that I do not know how to solve. The column
> needs to contain yes/no data for each record.
> Any help for a struggling newbie?

It is always wise to include the error message you get. In this case it
was very easy for me to reproduce the error, but sometimes this is
may be impossible with access to the database.

The best place to find out the syntax for a command, is to look it up
in Books Online, which comes with SQL Server. In the book T-SQL Syntax
Reference you find all T-SQL commands described with syntax diagrams.
Sure, the diagram for ALTER TABLE may seem unwieldy, since you can
alter a table in many ways. On the flip side, there are also examples
for the most common uses of the command.

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

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

Altering linked tables

Greetings,
I am using an Access mdb with all linked tables in MSDE. I know that you
can't change linked tables from the MDB, so I created an ADP that imports
all the tables, so that I can alter them in the ADP. The ADP let's me add a
column to a table, but when I open the mdb up again that column isn't there.
What do I do?
Thanks in advance.
Hi, Yair.
Drop the link, then recreate the link to the table. An external table's
structure and connection properties are only recorded at the time of
linking, so any later changes that you make to a linked table's structure
(i.e., add/change/delete/rename fields) or connection properties (i.e.,
add/change/delete database password) are unknown. Dropping and recreating
the link will re-establish the correct properties needed to access the data
in the external table.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I am using an Access mdb with all linked tables in MSDE. I know that you
> can't change linked tables from the MDB, so I created an ADP that imports
> all the tables, so that I can alter them in the ADP. The ADP let's me add
a
> column to a table, but when I open the mdb up again that column isn't
there.
> What do I do?
> Thanks in advance.
>
|||Thanks Gunny.
How do I drop the link and relink?
Will it affect all my forms and reports?
I'll check the help too but if it's an easy answer...
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Thanks. I used the linked table manager to refresh the tables an it worked
perfectly.
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Check out http://www.mvps.org/access/tables/tbl0010.htm at "The Access Web"
for one approach to relinking ODBC tables, or see
http://members.rogers.com/douglas.j...LessLinks.html for how to do
it without requiring a DSN.
Assuming you do it when you first start up the application, it won't affect
your forms or reports unless table changes have occurred, and your forms or
reports reference fields or tables that are no longer present.
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss#HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>
|||Hi, Yair.

> How do I drop the link and relink?
Select the linked table in the database window with your mouse and hit the
<DELETE> key. Then use the menu "File -> Get External Data -> Link Tables"
and browse for the file that contains the table that you want to link to,
then follow the prompts in the dialog window just like you did when you
originally linked the table.

> Will it affect all my forms and reports?
Sort of. It will allow you to add this new field to all of the forms and
reports bound to this table, and any queries and Recordsets that utilize the
table, but won't automatically make these changes for you.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss%23HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>

Altered SQL table locked for edits in Access 2002 front-end

I have altered SQL tables and then relinked them to a
Access 2002 front-end and receive the following
message "The Microsoft Jet Database engine stopped the
process because you and another user are attempting to
change the same data at the same time"
When the SQL table is altered there are no users currently
connected to any of the SQL or Access Databases.
The SQL table being altered is related to many other SQL
tables.
The alter statements are adding new fields to the table.
Please help!How are you altering the SQLS tables? From Access or some other tool?
Access caches linked table schema locally, so if you are going to make
modifications to the table schema, you need to delete the existing
links in the FE, make your changes on the BE, and then re-link the
tables from scratch.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sat, 20 Dec 2003 13:53:56 -0800, "Bek"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>I have altered SQL tables and then relinked them to a
>Access 2002 front-end and receive the following
>message "The Microsoft Jet Database engine stopped the
>process because you and another user are attempting to
>change the same data at the same time"
>When the SQL table is altered there are no users currently
>connected to any of the SQL or Access Databases.
>The SQL table being altered is related to many other SQL
>tables.
>The alter statements are adding new fields to the table.
>Please help!

---

ALTER TABLE to Allow Null Values

I have an (Access 2003) database and I'm trying to update the schema of the database to allow null values in a column. The column already exists and currently will not allow null values. This is a distributed application (everyone has their own different MDB file) so I need to be able to modify the column through T-SQL.

My statement to try and do this is:
ALTER TABLE clients ALTER COLUMN state VARCHAR(255) NULL

However, when I view the table after running that SQL statement the table is still not allowing null values. Please don't tell me I need to drop the column before allowing null values.

Thanks,
Ryan

> I have an (Access 2003) database

Do you realize this group is about SQL Server?

AMB

|||Nope I just thought it was about T-SQL I didn't see that it was a sub-group of SQL Server. Sorry.
|||

No need to apologize.

There are differences between Access-SQL and T-SQL.

And of course, some Access applications use SQL Server for the backend (ADP Projects.) So at times, this would be the correct forumn. But for your particular question, one of the many Access forumns or NNTP groups 'might' be a better choice.

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

Hi

when I'm upgrading table schema with alter statement

I'm getting error like this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

can Anybody tell the solutiuon plz.

Thank u .

vizai

Please check to see if you have foreign key(s) referencing the column.|||

hi Waldrop

The column has a constraint

|||

If you have any View /UDF With SchemaBinding on this Base table , you are not allowed to change or Drop columns or Table Object..

If you want to alter the table first you need to remove the SchemaBinding in all the views/UDF and change the column on Base table then recreate the Views/UDFs With SchemaBinding.

If you have any Indexed Views when you remove the SchemaBinding all the Index on the Views will be removed, so you have to create them too..

|||

Hi mani

Thanks fo the reply.

How can I use schemabinding to drop the views / udf / indexed views using SMO.

How can I generate the drop script alone for all the views / udf / indexed views / constraints.

Can u help me in this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

Hi

when I'm upgrading table schema with alter statement

I'm getting error like this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

can Anybody tell the solutiuon plz.

Thank u .

vizai

Please check to see if you have foreign key(s) referencing the column.|||

hi Waldrop

The column has a constraint

|||

If you have any View /UDF With SchemaBinding on this Base table , you are not allowed to change or Drop columns or Table Object..

If you want to alter the table first you need to remove the SchemaBinding in all the views/UDF and change the column on Base table then recreate the Views/UDFs With SchemaBinding.

If you have any Indexed Views when you remove the SchemaBinding all the Index on the Views will be removed, so you have to create them too..

|||

Hi mani

Thanks fo the reply.

How can I use schemabinding to drop the views / udf / indexed views using SMO.

How can I generate the drop script alone for all the views / udf / indexed views / constraints.

Can u help me in this

ALTER TABLE

I sometimes use the ALTER TABLe to add certain fields in my table. I need to
do it programatically. I will not get into why eventhough I have access to
Enterprise manager and can use that to do it that way.
My question is I'd like to know if there is syntax that I can use when I
ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the tabl
e
to place this new column. For example placing the new field in 3rd position
or place in a table of 20 fields. I'd like to specify where in the table to
place this new field...
thanks in advance...AFAIK you can not
If you look in EM when you do it (save change script icon) you will see that
EM creates a new table drops the old one and renames the newly created one t
o
the original one
http://sqlservercode.blogspot.com/
"Angel" wrote:

> I sometimes use the ALTER TABLe to add certain fields in my table. I need
to
> do it programatically. I will not get into why eventhough I have access to
> Enterprise manager and can use that to do it that way.
> My question is I'd like to know if there is syntax that I can use when I
> ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the ta
ble
> to place this new column. For example placing the new field in 3rd positio
n
> or place in a table of 20 fields. I'd like to specify where in the table t
o
> place this new field...
> thanks in advance...|||And at the same time preserving the data?
"SQL" wrote:
> AFAIK you can not
> If you look in EM when you do it (save change script icon) you will see th
at
> EM creates a new table drops the old one and renames the newly created one
to
> the original one
> http://sqlservercode.blogspot.com/
>
> "Angel" wrote:
>|||Column order is completely irrelevant. If you need this for presentation
purposes, simply do it on the client. If you need this for documentation, us
e
the INFORMATION_SCHEMA system views.
ML|||> I'd Like to specify where in the table
> to place this new column.
Sorry, you cannot do this with ALTER TABLE.
You can, of course, try to do what Enterprise Manager does:
http://www.aspfaq.com/2528|||> And at the same time preserving the data?
Yep, it does. See http://www.aspfaq.com/2528|||Well if you look at the change script you will see that the isolation level
is SERIALIZABLE
This is the highest level, no updates,inserts or deletes can happen on this
table while this script runs
http://sqlservercode.blogspot.com/
"Angel" wrote:
> And at the same time preserving the data?
> "SQL" wrote:
>|||Assuming you can get this to work, don't forget to use sp_refreshview on all
the views based on the table you alter. If you don't, your views may start
returning unexpected results.
"Angel" wrote:

> I sometimes use the ALTER TABLe to add certain fields in my table. I need
to
> do it programatically. I will not get into why eventhough I have access to
> Enterprise manager and can use that to do it that way.
> My question is I'd like to know if there is syntax that I can use when I
> ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the ta
ble
> to place this new column. For example placing the new field in 3rd positio
n
> or place in a table of 20 fields. I'd like to specify where in the table t
o
> place this new field...
> thanks in advance...|||On Thu, 22 Sep 2005 09:37:07 -0700, mike wrote:

>Assuming you can get this to work, don't forget to use sp_refreshview on al
l
>the views based on the table you alter. If you don't, your views may start
>returning unexpected results.
Hi Mike,
AFAIK, that's only necessary for views defined as SELECT * FROM ...
And since you shouldn't use SELECT * in production code anyway, there's
no need to worry.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ALTER SQL Server 2000 functions on SQL Express

I installed SQL Server Management Express and try to access to a SQL Server 2000 remotly.

I connected correctly but when I try to alter an existing function, I get the following error message:

TITLE: Microsoft SQL Server Management Studio Express

Property AnsiNullsStatus is not available for UserDefinedFunction '[dbo].[functionName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)

Note that functionName contain the correct function name.

Can someone confirm me that SQL Server Managmt Express can not alter function from previous SQL version or may I need to set some parameters on Server 2000.

Thanks

I'm moving this to the Tools forum.

Mike

Alter Database while "Suspect"

Hi

I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.

and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)

(i tried starting with -f -c, but no go)

thanks in advance
desCheck in your virus scan software to see if it ignores *.mdf, *.ndf and *.ldf. I am assuming this is on startup of the database? Say after recycling the SQL Server service, or rebooting the machine?

Also, you will want to do some diagnostics on the disk (check with hardware vendor), in order to make sure the disk has not suddenly gone bad.

Good luck, and let us know what happens.|||What had happened was the folder/drive I put the index file on was set to compress contents (accidentally)...I figure the windows compression had a hold on it...happened after rebooting the machine (I thought it took real long to build those indexes - now i know why!)

Disk seems fine though...

Thanks for advice
cheers
des

(what happened ultimately was db loss & a 12 hour snapshot delivery...eughh. wish i couldve just removed the index file somehow...)|||I thought I read it somewhere not to use compression on any files SQL touches, maybe with the exception of the error logs. You can lock out any interactive access to the data and log folders to prevent future mishaps.

ALTER COLUMN

I have an access database that I'm splitting its back-end to be located in
SQL server. One column in one of the access tables was autonumber. For
maitenance purposes, some of the rows in this column have been deleted. When
I converted the back-end to SQL, this column is defined as INT and I can't
define it as INT IDFENTITY (1,1) because of the rows that have been taken
out. I need to define this column as identity, so everytime the user adds a
new record, this column will generate an auto number. I tried the following
syntax with no luck. Any ideas?
alter table tbl_Reservation
with nocheck
ALTER COLUMN [reservation #] int IDENTITY (1,1)
constraint PK_ReservationNo primary key clustered([reservation #])
TSYou can't add or drop the IDENTITY column. You can add a new column with
the IDENTITY() property.
Or you can let Enterprise Manager do it, though I don't recommend this if
the table is of any consequential size (see http://www.aspfaq.com/2528 for
an example of the kind of thing Enterprise Manager does behind your back).
A
"TS" <TS@.discussions.microsoft.com> wrote in message
news:E29E76A2-4058-4099-A458-E3D600484499@.microsoft.com...
>I have an access database that I'm splitting its back-end to be located in
> SQL server. One column in one of the access tables was autonumber. For
> maitenance purposes, some of the rows in this column have been deleted.
> When
> I converted the back-end to SQL, this column is defined as INT and I can't
> define it as INT IDFENTITY (1,1) because of the rows that have been taken
> out. I need to define this column as identity, so everytime the user adds
> a
> new record, this column will generate an auto number. I tried the
> following
> syntax with no luck. Any ideas?
> alter table tbl_Reservation
> with nocheck
> ALTER COLUMN [reservation #] int IDENTITY (1,1)
> constraint PK_ReservationNo primary key clustered([reservation #])
>
> --
> TS|||I have come across the same problem. I cannot get the syntax right to create
an
IDENTITY (1,1) property on an existing INT column.
If it can be done in Enterprise Manager through the GUI, then there HAS to
be a way to do it in T-SQL.
Todd
"TS" wrote:

> I have an access database that I'm splitting its back-end to be located in
> SQL server. One column in one of the access tables was autonumber. For
> maitenance purposes, some of the rows in this column have been deleted. Wh
en
> I converted the back-end to SQL, this column is defined as INT and I can't
> define it as INT IDFENTITY (1,1) because of the rows that have been taken
> out. I need to define this column as identity, so everytime the user adds
a
> new record, this column will generate an auto number. I tried the followin
g
> syntax with no luck. Any ideas?
> alter table tbl_Reservation
> with nocheck
> ALTER COLUMN [reservation #] int IDENTITY (1,1)
> constraint PK_ReservationNo primary key clustered([reservation #])
>
> --
> TS|||> If it can be done in Enterprise Manager through the GUI, then there HAS to
> be a way to do it in T-SQL.
Yes, there is. Run profiler while you do it in EM, and prepare to be
amazed. Memorize the script. Rinse. Repeat. Good luck.
And FWIW, there are a lot of things that can be done through the EM GUI.
Not all of them are good, and not all of them are done the best/right way.
Be careful where you learn from. :-)|||I dropped the identity column with no problems, created another one with the
same name and since this column serves only as unique identifier, this
solution didn't hurt in any way. The problem is the identity column is not
generated in the front-end when adding a new record !! Any idea'
--
TS
"Aaron Bertrand [SQL Server MVP]" wrote:

> You can't add or drop the IDENTITY column. You can add a new column with
> the IDENTITY() property.
> Or you can let Enterprise Manager do it, though I don't recommend this if
> the table is of any consequential size (see http://www.aspfaq.com/2528 for
> an example of the kind of thing Enterprise Manager does behind your back).
> A
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:E29E76A2-4058-4099-A458-E3D600484499@.microsoft.com...
>
>|||On Fri, 5 Aug 2005 12:15:05 -0700, TS wrote:

>I have an access database that I'm splitting its back-end to be located in
>SQL server. One column in one of the access tables was autonumber. For
>maitenance purposes, some of the rows in this column have been deleted. Whe
n
>I converted the back-end to SQL, this column is defined as INT and I can't
>define it as INT IDFENTITY (1,1) because of the rows that have been taken
>out. I need to define this column as identity, so everytime the user adds a
>new record, this column will generate an auto number. I tried the following
>syntax with no luck. Any ideas?
Hi TS,
Create the table with IDENTITY column. Use the SET IDENTITY_INSERT
command to allow specification of the values in the IDENTITY column,
then port your data from Access to SQL Server. Now reset the
IDENTITY_INSERT operation to make SQL Server generate new identity
values for future inserts.
I think that will do the trick.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The identity column will be set appropriately even if not set in the
front-end. Actually, setting it would generate an error.
ML|||What do you mean "generated"? It is supposed to be generated on the
backend. What front-end and can you be more specific?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:DF38835E-69F8-4E35-ADBB-D339A33E910D@.microsoft.com...
>I dropped the identity column with no problems, created another one with
>the
> same name and since this column serves only as unique identifier, this
> solution didn't hurt in any way. The problem is the identity column is not
> generated in the front-end when adding a new record !! Any idea'
> --
> TS
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Alter a SQL table

I used the MS Access 2000 Upsize wizard to create a Client/server
application. After the wizard ran, the original table names had become
the linked tables and then there were duplicates of the the tables
named xxx_local.

I realized later that I needed to add some fields to a table and
couldn't do that in Access (couldn't edit the linked tables). So I used
the Query Analyzer and created a script to add the fields to my table.
It looked fine in Query Analyzer, but back in Access the fields were
added to xxx_local and not xxx, the linked table. My forms and reports
are built on xxx, not xxx_local.

How do I do this correctly?

Janejfrasier wrote:

Quote:

Originally Posted by

I used the MS Access 2000 Upsize wizard to create a Client/server
application. After the wizard ran, the original table names had become
the linked tables and then there were duplicates of the the tables
named xxx_local.
>
I realized later that I needed to add some fields to a table and
couldn't do that in Access (couldn't edit the linked tables). So I used
the Query Analyzer and created a script to add the fields to my table.
It looked fine in Query Analyzer, but back in Access the fields were
added to xxx_local and not xxx, the linked table. My forms and reports
are built on xxx, not xxx_local.
>
How do I do this correctly?
>
Jane

Anytime I've added fields to a linked table in Access, I've always had
to refresh the links (using the Linked Table Manager) in order to see
them on the Access side. I'm not sure if the fact that I seldom use
Query Anazlyer to add fields to a table or not has anything to do with
it (I just go into Enterprise Manager and make the changes there). Are
you sure you're referencing the desired table in your script?

Molly J. Fagan|||I re-linked the table in Access and that did the trick. Thanks.

Jane

mollyf@.hotmail.com wrote:

Quote:

Originally Posted by

jfrasier wrote:

Quote:

Originally Posted by

I used the MS Access 2000 Upsize wizard to create a Client/server
application. After the wizard ran, the original table names had become
the linked tables and then there were duplicates of the the tables
named xxx_local.

I realized later that I needed to add some fields to a table and
couldn't do that in Access (couldn't edit the linked tables). So I used
the Query Analyzer and created a script to add the fields to my table.
It looked fine in Query Analyzer, but back in Access the fields were
added to xxx_local and not xxx, the linked table. My forms and reports
are built on xxx, not xxx_local.

How do I do this correctly?

Jane

>
Anytime I've added fields to a linked table in Access, I've always had
to refresh the links (using the Linked Table Manager) in order to see
them on the Access side. I'm not sure if the fact that I seldom use
Query Anazlyer to add fields to a table or not has anything to do with
it (I just go into Enterprise Manager and make the changes there). Are
you sure you're referencing the desired table in your script?
>
Molly J. Fagan

Allowing users access to their DB only?

Hey all.
I'm trying to set up SQL Server so that people with Enterprise Mgr can create a DB registration to their DB only (sql.yoursite.com). Are there any tutorials out there for doing this?
Thanks for the help!If they connect using SQL authentication, I'm pretty sure that they'll only see the databases that they can access.

-PatP|||Thanks for the info. Part of the problem is keeping users from creating a registration to the main SQL server na dlet them only get to their DB. In a shared hosting environment, you definately don't want people seeing all the DBs availble.|||There aren't any tutorials that I'm aware of. The problem with this is that you have to be a security administrator to add a user. My suggestion would be to put a procedure in your model database that allows people to add a user.

It would insert this user, the database name, and a datetime into a "utility database". You could have a job running on the server that hits that table every 15 minutes and adds users.

If you want to get fancy, you can have the stored procedure/form have different permission levels also, so they can have more granular control over what they let their users do. Although if you have this, you will want to have the ability to edit them also.

They would just have to know they need to wait 15 minutes or so after adding a new user for it to take effect.

Allowing user to connect to his database

Hi
I would like to give user access to his database running on mssql 2000 using
enterprise manager.
But... the user must not be able to see any other databases on that server,
I just want him to be able to see his own database and manage it.
Is this possible without installing another instance of sql server and give
that user access to that instance?
and if so how does the licensing work if you have many instances of sql
running on one server?
Regards
GauiThat's a known limitation with Enterprise Manager. It was designed for the
system admin to use to manage all databases.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Just trying to do this aswell.. Never mind will have to
find another way. Do you know if there is a seperate tool
for visually creating stored procedures other than VIEW /
SP parts of Enterprise manager?

>--Original Message--
>That's a known limitation with Enterprise Manager. It
was designed for the
>system admin to use to manage all databases.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>

allowing outside party to access database

What would be the best way to allow a third party to have read only access to
part of our database. We both collect do not email information and they want
to be able to see what we have and load it into their system to supplement
what they have collected.
We're thinking about a readonly view of certain fields in one table. Would
using xml be more secure than setting up a linked server? Or is there a
better way?
Thanks,
Dan D.
Hi
More secure and easier. If they really need linked server access, put data
for them into a seperate DB that gets updated ever xxxminutes/hours. You
don't want them in your main DB.
Regards
Mike
"Dan D." wrote:

> What would be the best way to allow a third party to have read only access to
> part of our database. We both collect do not email information and they want
> to be able to see what we have and load it into their system to supplement
> what they have collected.
> We're thinking about a readonly view of certain fields in one table. Would
> using xml be more secure than setting up a linked server? Or is there a
> better way?
> Thanks,
> --
> Dan D.
|||I'm not sure I understand. Are you saying that the linked server is more
secure and easier? Or that something else is more secure and easier but if
they have to have a linked server to put it into a separate db?
Thanks,
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> More secure and easier. If they really need linked server access, put data
> for them into a seperate DB that gets updated ever xxxminutes/hours. You
> don't want them in your main DB.
> Regards
> Mike
> "Dan D." wrote:
|||Hi
A Linked server is more secure. An XML file has to be put somewhere that can
be downloaded from. A linked server, against a different DB (or better,
server) makes it as easy as can get.
If you see that more 3rd parties need access, then XML is better, but some
sort of development effort needs to happen in this case.
Regards
Mike
"Dan D." wrote:
[vbcol=seagreen]
> I'm not sure I understand. Are you saying that the linked server is more
> secure and easier? Or that something else is more secure and easier but if
> they have to have a linked server to put it into a separate db?
> Thanks,
> Dan D.
> "Mike Epprecht (SQL MVP)" wrote:
|||Thanks.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
|||Someone mentioned using net libraries as a solution. Do you know anything
about them?
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:

allowing outside party to access database

What would be the best way to allow a third party to have read only access to
part of our database. We both collect do not email information and they want
to be able to see what we have and load it into their system to supplement
what they have collected.
We're thinking about a readonly view of certain fields in one table. Would
using xml be more secure than setting up a linked server? Or is there a
better way?
Thanks,
--
Dan D.Hi
More secure and easier. If they really need linked server access, put data
for them into a seperate DB that gets updated ever xxxminutes/hours. You
don't want them in your main DB.
Regards
Mike
"Dan D." wrote:
> What would be the best way to allow a third party to have read only access to
> part of our database. We both collect do not email information and they want
> to be able to see what we have and load it into their system to supplement
> what they have collected.
> We're thinking about a readonly view of certain fields in one table. Would
> using xml be more secure than setting up a linked server? Or is there a
> better way?
> Thanks,
> --
> Dan D.|||I'm not sure I understand. Are you saying that the linked server is more
secure and easier? Or that something else is more secure and easier but if
they have to have a linked server to put it into a separate db?
Thanks,
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> More secure and easier. If they really need linked server access, put data
> for them into a seperate DB that gets updated ever xxxminutes/hours. You
> don't want them in your main DB.
> Regards
> Mike
> "Dan D." wrote:
> > What would be the best way to allow a third party to have read only access to
> > part of our database. We both collect do not email information and they want
> > to be able to see what we have and load it into their system to supplement
> > what they have collected.
> >
> > We're thinking about a readonly view of certain fields in one table. Would
> > using xml be more secure than setting up a linked server? Or is there a
> > better way?
> >
> > Thanks,
> > --
> > Dan D.|||Hi
A Linked server is more secure. An XML file has to be put somewhere that can
be downloaded from. A linked server, against a different DB (or better,
server) makes it as easy as can get.
If you see that more 3rd parties need access, then XML is better, but some
sort of development effort needs to happen in this case.
Regards
Mike
"Dan D." wrote:
> I'm not sure I understand. Are you saying that the linked server is more
> secure and easier? Or that something else is more secure and easier but if
> they have to have a linked server to put it into a separate db?
> Thanks,
> Dan D.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > More secure and easier. If they really need linked server access, put data
> > for them into a seperate DB that gets updated ever xxxminutes/hours. You
> > don't want them in your main DB.
> >
> > Regards
> > Mike
> >
> > "Dan D." wrote:
> >
> > > What would be the best way to allow a third party to have read only access to
> > > part of our database. We both collect do not email information and they want
> > > to be able to see what we have and load it into their system to supplement
> > > what they have collected.
> > >
> > > We're thinking about a readonly view of certain fields in one table. Would
> > > using xml be more secure than setting up a linked server? Or is there a
> > > better way?
> > >
> > > Thanks,
> > > --
> > > Dan D.|||Thanks.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
> > I'm not sure I understand. Are you saying that the linked server is more
> > secure and easier? Or that something else is more secure and easier but if
> > they have to have a linked server to put it into a separate db?
> >
> > Thanks,
> >
> > Dan D.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > More secure and easier. If they really need linked server access, put data
> > > for them into a seperate DB that gets updated ever xxxminutes/hours. You
> > > don't want them in your main DB.
> > >
> > > Regards
> > > Mike
> > >
> > > "Dan D." wrote:
> > >
> > > > What would be the best way to allow a third party to have read only access to
> > > > part of our database. We both collect do not email information and they want
> > > > to be able to see what we have and load it into their system to supplement
> > > > what they have collected.
> > > >
> > > > We're thinking about a readonly view of certain fields in one table. Would
> > > > using xml be more secure than setting up a linked server? Or is there a
> > > > better way?
> > > >
> > > > Thanks,
> > > > --
> > > > Dan D.|||Someone mentioned using net libraries as a solution. Do you know anything
about them?
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that can
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
> > I'm not sure I understand. Are you saying that the linked server is more
> > secure and easier? Or that something else is more secure and easier but if
> > they have to have a linked server to put it into a separate db?
> >
> > Thanks,
> >
> > Dan D.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > More secure and easier. If they really need linked server access, put data
> > > for them into a seperate DB that gets updated ever xxxminutes/hours. You
> > > don't want them in your main DB.
> > >
> > > Regards
> > > Mike
> > >
> > > "Dan D." wrote:
> > >
> > > > What would be the best way to allow a third party to have read only access to
> > > > part of our database. We both collect do not email information and they want
> > > > to be able to see what we have and load it into their system to supplement
> > > > what they have collected.
> > > >
> > > > We're thinking about a readonly view of certain fields in one table. Would
> > > > using xml be more secure than setting up a linked server? Or is there a
> > > > better way?
> > > >
> > > > Thanks,
> > > > --
> > > > Dan D.

allowing outside party to access database

What would be the best way to allow a third party to have read only access t
o
part of our database. We both collect do not email information and they want
to be able to see what we have and load it into their system to supplement
what they have collected.
We're thinking about a readonly view of certain fields in one table. Would
using xml be more secure than setting up a linked server? Or is there a
better way?
Thanks,
--
Dan D.Hi
More secure and easier. If they really need linked server access, put data
for them into a seperate DB that gets updated ever xxxminutes/hours. You
don't want them in your main DB.
Regards
Mike
"Dan D." wrote:

> What would be the best way to allow a third party to have read only access
to
> part of our database. We both collect do not email information and they wa
nt
> to be able to see what we have and load it into their system to supplement
> what they have collected.
> We're thinking about a readonly view of certain fields in one table. Would
> using xml be more secure than setting up a linked server? Or is there a
> better way?
> Thanks,
> --
> Dan D.|||I'm not sure I understand. Are you saying that the linked server is more
secure and easier? Or that something else is more secure and easier but if
they have to have a linked server to put it into a separate db?
Thanks,
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> More secure and easier. If they really need linked server access, put data
> for them into a seperate DB that gets updated ever xxxminutes/hours. You
> don't want them in your main DB.
> Regards
> Mike
> "Dan D." wrote:
>|||Hi
A Linked server is more secure. An XML file has to be put somewhere that can
be downloaded from. A linked server, against a different DB (or better,
server) makes it as easy as can get.
If you see that more 3rd parties need access, then XML is better, but some
sort of development effort needs to happen in this case.
Regards
Mike
"Dan D." wrote:
[vbcol=seagreen]
> I'm not sure I understand. Are you saying that the linked server is more
> secure and easier? Or that something else is more secure and easier but if
> they have to have a linked server to put it into a separate db?
> Thanks,
> Dan D.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Thanks.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that c
an
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
>|||Someone mentioned using net libraries as a solution. Do you know anything
about them?
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> A Linked server is more secure. An XML file has to be put somewhere that c
an
> be downloaded from. A linked server, against a different DB (or better,
> server) makes it as easy as can get.
> If you see that more 3rd parties need access, then XML is better, but some
> sort of development effort needs to happen in this case.
> Regards
> Mike
> "Dan D." wrote:
>

Allowing Access to SQL over the Internet

Hello Everyone,
I was wondering if people could point me to some documentation about
allowing access to SQL Server over the Internet. Any posts, links,
articles, pros/cons, issues to consider, potential problems, strategies or
recommendations are greatly appreciated!
Best Regards,
BradIn books on line there is a document with HTML in the title search for
that..
Many people do NOT allow direct exposure of their database on the internet
due to security issues...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23rx11KTfFHA.3788@.tk2msftngp13.phx.gbl...
> Hello Everyone,
> I was wondering if people could point me to some documentation about
> allowing access to SQL Server over the Internet. Any posts, links,
> articles, pros/cons, issues to consider, potential problems, strategies or
> recommendations are greatly appreciated!
> Best Regards,
> Brad
>

Allowing access to Enterprise Manager without giving admin rights.

I have a user that will be doing specific updates to a specific table
in SQL. Rather than have them working at the server when these needed
to be done, I thought I would install SQL Admin Tools at their
workstation. Does anyone know if I can do this, and allow him use of
Enterprise Manager to access this table, without giving him admin
rights. He will need to import an excel file into this table
periodically.
Thanks.There's no need to give him Admin rights in order to update a table on
occaison.
Simply grant him insert/update/delete permissions to the specific table.
Then write some vb code to insert the data from Excel to SQL.
321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/?id=321686
Or
Create a DTS Package on the server. Have the user put his Excel file on a
server share, and then
periodically have the DTS package scheduled to run and process the data.
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data
Transformation
http://support.microsoft.com/?id=319951
Or
You could simply give him db_datareader, db_datawriter in the database.
See Fixed Database Roles in SQL Books Online
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

allowing access to database only through stored procedures

I have read that it is possible to configure sql server express so that the database can only beaccessed through stored procedures. Can anyone tell me how to do this.

Many thanks.

martin

Really? Where is the original article which mentions this? I guess it actually talks about security management in SQL. Yes you can limit permissions of logins (or users after mapping the logins to databases). Image such a scenario: you want a login to do some specific updates to a table, but you do not want to grant UPDATE permission on the table to the login (if you do so, the login can do any UPDATE as he like on the table). In this case you can create a stored procedure to do the specific update, and then you give the EXECUTE permission to the login. This is one of the advantages of SPs.

Stored procedures offer numerous advantages. They can:

Share application logic with other applications, thereby ensuring consistent data access and modification.

Stored procedures can encapsulate business functionality. Business rules or policies encapsulated in stored procedures can be changed in a single location. All clients can use the same stored procedures to ensure consistent data access and modification.

|||

thank you for your reply

martin

Allowing a user access to only a few tables

With MS SQL 2000 Enterprise Manager, is there a way to allow a user access
to only a few tables, but deny the user access to the rest without having to
go to all of the tables and denying access? The database has roughly 50
tables, but only 3 should be granted to the new user, so as you can see it
would be a painstaking task to manually do this with the *cough* mouse. Or,
if I can run some sort of grant script, that would work too. Thank you!SELECT 'DENY ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO <username>'
FROM SYSOBJECTS ORDER BY NAME

SELECT 'GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO
<username>' FROM SYSOBJECTS ORDER BY NAME

Highlight the results you want and run. You can alternate <username>
out for public or a specific group name. Good luck.

****************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com

Please remove NOMORESPAM before replying.

This posting is provided "as is" with
no warranties and confers no rights.

****************************************

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Andy S." <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:40438ed0$0$195$75868355@.news.frii.net...
> SELECT 'DENY ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO <username>'
> FROM SYSOBJECTS ORDER BY NAME
> SELECT 'GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO
> <username>' FROM SYSOBJECTS ORDER BY NAME
> Highlight the results you want and run. You can alternate <username>
> out for public or a specific group name. Good luck.
> ****************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> This posting is provided "as is" with
> no warranties and confers no rights.
> ****************************************
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Hello Andy,

Thank you for the help. I've run that script, and in the Expr1 column the
first line is:
DENY ALL|SELECT|INSERT|UPDATE|DELETE ON Aliases TO vms

So I have selected the line and selected "run" from the list. Is this the
propper way of denying the user "vms" to the aliases table? The reason I ask
is because when I check the permissions on that table, the user still has
all options (select, insert, etc.) checked. Again, thank you for the help!