Prinitng Tables

Is there a way within Enterprise Manager to print the contents of a Table?
I could of sworn there was a way...I think I just forget.
Thanks!Use Query Analyzer. You can choose results to text or results to grid and
then print the results pane.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:9F727D28-43B4-4715-9B19-F0D223FFB6DE@.microsoft.com...
> Is there a way within Enterprise Manager to print the contents of a
> Table?
> I could of sworn there was a way...I think I just forget.
> Thanks!|||You can highlight columns or rows and copy/paste them to Excel. It will even
carry over the column names.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:9F727D28-43B4-4715-9B19-F0D223FFB6DE@.microsoft.com...
> Is there a way within Enterprise Manager to print the contents of a
> Table?
> I could of sworn there was a way...I think I just forget.
> Thanks!sql

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
John
Primary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon
|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?
|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.c om...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?
|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ
|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ
|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:

>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where
a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.p
hx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:

>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
--
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

primary keys have gotten out of sequence

In Enterprise Manager, my primary keys used to be all in sequence. After de
leting a few pk and adding new ones, they are no longer in sequence.
In Enterprise Manager, I would like to be able to view "Return all rows" wit
h the primary keys in numerical order.
What is the best way to accomplish this?
Thank you in advance,
TRTimothy Ross wrote:
> In Enterprise Manager, my primary keys used to be all in sequence. After
deleting a few pk and adding new ones, they are no longer in sequence.
> In Enterprise Manager, I would like to be able to view "Return all rows" w
ith the primary keys in numerical order.
> What is the best way to accomplish this?
> Thank you in advance,
> TR
Tables have no inherent order. The only way to fix the order you see is
to use an ORDER BY clause in your SELECT statements. Enterprise Manager
won't do that for you - you need to write your own query. Preferably
use Query Analyzer instead because it's a much more powerful tool.
You'll need to familiarize yourself with SQL syntax first but that's
not so hard for the basic stuff. For example:
SELECT col1, col2, col3, ...
FROM your_table
ORDER BY col1 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David - Thank you for your quick response.
I understand that I can use query analyzer for sorting. I have actually
used Microsoft Access because this will allow sorting very easily.
I also understand that the primary keys really do not need to be displayed
in any particular order.
I have been told there is a command that will re-order my primary keys
sequentially so that I can view them in Enterprise Manager in the correct
order - it has something to do with INDEX on the column, but I haven't found
any information on how to accomplish his.
CREATE INDEX indexname tablename (column) -- doesn't reorder the primary key
the way that I want it to - I was wondering if there was a way to accomplish
this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146170512.392795.12240@.i40g2000cwc.googlegroups.com...
> Timothy Ross wrote:
> Tables have no inherent order. The only way to fix the order you see is
> to use an ORDER BY clause in your SELECT statements. Enterprise Manager
> won't do that for you - you need to write your own query. Preferably
> use Query Analyzer instead because it's a much more powerful tool.
> You'll need to familiarize yourself with SQL syntax first but that's
> not so hard for the basic stuff. For example:
> SELECT col1, col2, col3, ...
> FROM your_table
> ORDER BY col1 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
<ross_timothy@.hotmail.com> wrote:
>I have been told there is a command that will re-order my primary keys
>sequentially so that I can view them in Enterprise Manager in the correct
>order - it has something to do with INDEX on the column, but I haven't foun
d
>any information on how to accomplish his.
If you have a clustered index (or primary key) on the table, then the
physical and logical order will be the same.
But even then, a select statement without an order-by does not seem to
guarantee things will display in order. Just why not, I do not really
understand. May have to do with reuse of freed pages and order of
insertion, so the logical and physical *page* order are not the same,
irrespective of the logical and physical *row* orders.
Josh|||> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
SQL Server is free to return data in any sequence (e.g. most efficient
manner) unless the query contains ORDER BY. For example, there is a
'merry-go-round' scan feature that allows multiple concurrent queries to
'piggyback' on a scan already in progress. Once the later queries retrieve
the last row, the scan resumes at the beginning and continues until the
initially retrieved row is encountered. This could result in rows returned
in an arbitrary order.
It's good to have an understanding of the physical implementation for
performance analysis. However, you can rely only on the result described by
the SQL query.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:gb88529oa6vkkdom9dgbtv9gfhhkh16u7e@.
4ax.com...
> On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
> <ross_timothy@.hotmail.com> wrote:
> If you have a clustered index (or primary key) on the table, then the
> physical and logical order will be the same.
> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
> Josh
>

primary key wont auto-update

I discovered the cause:
in SQL Server Enterprise Manager, open up db tree, right click the table, go - 'design' and set 'Identity' to yes.You were confusing "Primary Key" and "Identity Value". They are two different concepts which just happen to be frequently applied to the same column.

Primary file & tan Log grow crazy!

HI:
I have a Table about 3GB in size. Whenever I try to add a column or set
a primary key to this table from the Enterprise Manager, the operation
would take a long time and the MDF and LOG file will just keep growing
crazy (more then 3GB) that it finanlly eats up all my hard disk space
and I have to kill the process.
So, how many HD space does it need to modify a 3GB Table (eg. add a
column). Actually what is the ratio? Is there any better way to modify
a large Table?
I have also enable the Auto-Shrink properties of the Table but still no
use.
Much appreciate for any advise.JJ wrote:
> HI:
> I have a Table about 3GB in size. Whenever I try to add a column or
> set a primary key to this table from the Enterprise Manager, the
> operation would take a long time and the MDF and LOG file will just
> keep growing crazy (more then 3GB) that it finanlly eats up all my
> hard disk space and I have to kill the process.
> So, how many HD space does it need to modify a 3GB Table (eg. add a
> column). Actually what is the ratio? Is there any better way to modify
> a large Table?
> I have also enable the Auto-Shrink properties of the Table but still
> no use.
> Much appreciate for any advise.
You should always make these types of changes using good old ALTER TABLE
commands from your query tool and avoid using SQL EM for these tasks.
While SQL EM seems like an easy alternative, depending on the operation,
SQL EM may actually be creating a copy of the table with the changes and
manually migrating the data. To see what SQL EM is doing you can either
watch the activity in Profiler or script out the changes in SQL EM.
Setting a PK in a table should not cause a recreate of the table, but if
you create the PK as clustered, then SQL Server has to reorg the entire
3GB table, which could be quite time consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use change scripts for that, EMs doing something a useless stuff around
the scripts. If you make your changes in a DDL scripts you should be
able to execute that much easier, even because the addition of a
primary key has no need to recreate the table as it is in the case of
an identity column.
HTH, Jens Suessmeyer.

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCH
Check out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegr oups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>
|||Hi Peter
Did you follow: http://support.microsoft.com/default...b;en-us;303774
John
"Peter CCH" wrote:

> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>
|||I tried:
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH
|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCHCheck out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegroups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||Hi Peter
Did you follow: [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;303774[/ur
l]
John
"Peter CCH" wrote:

> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||I tried:
---
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
---
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCHCheck out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegroups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||Hi Peter
Did you follow: http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
John
"Peter CCH" wrote:
> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||I tried:
---
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
---
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Preventing Enterprise Manager access from Windows users

Hello,
Scenario: SQL 2000 or 2005 server in an ADS domain. ADS groups are created
as SQL logins, and those logins are created as database users with datareade
r
and/or datawriter roles to a specific database. An intranet web site is
created to allow the ADS users in those ADS groups access to the data.
Problem: How do I prevent the ADS users in those ADS groups from running
Enterprise Manager (or creating their own ODBC connections, etc.) and messin
g
with my database? Or, put another way, how do I prevent the users from using
their own interface to the database?
Thanks much,
-TonyA common practice for something like this would be too...
1. Create Stored Procedures that the application uses to manipulate data.
2. Assign those groups Execute Permissions on the Stored Procedures.
3. Deny Data Reader and Data Writer to those groups.
If you are using ADHOC in your program this is going to be a little tougher.
What do you think?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:ADF6BDE5-11D7-496E-994D-F9B56EF645E3@.microsoft.com...
> Hello,
> Scenario: SQL 2000 or 2005 server in an ADS domain. ADS groups are created
> as SQL logins, and those logins are created as database users with
> datareader
> and/or datawriter roles to a specific database. An intranet web site is
> created to allow the ADS users in those ADS groups access to the data.
> Problem: How do I prevent the ADS users in those ADS groups from running
> Enterprise Manager (or creating their own ODBC connections, etc.) and
> messing
> with my database? Or, put another way, how do I prevent the users from
> using
> their own interface to the database?
> Thanks much,
> -Tony
>|||Warren,
Thanks for the info. That does sound like a good solution. I'm assuming this
would allow users to run Enterprise Manager and see my databases and objects
listed, but not interact with them.
BTW, these applications are web apps using .net. So, perhaps another
alternative would be for me to autherize and authenticate users to the web
site using IIS and ADS groups, then use a SQL login and password (encrypted
in the web.config file) to handle db access. Adding your suggestion to this,
I could restrict the SQL login to execute only the needed sprocs.
Seems like this would prevent any access to my database by those ADS users
via Enterprise Manager or whatever. If the SQL login should be hacked, at
least it would only allow execute on the sprocs and nothing else.
Thanks,
-Tony
"Warren Brunk" wrote:

> A common practice for something like this would be too...
> 1. Create Stored Procedures that the application uses to manipulate data.
> 2. Assign those groups Execute Permissions on the Stored Procedures.
> 3. Deny Data Reader and Data Writer to those groups.
> If you are using ADHOC in your program this is going to be a little toughe
r.
> What do you think?
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:ADF6BDE5-11D7-496E-994D-F9B56EF645E3@.microsoft.com...
>
>|||Hello Tony
Please drop me a note if the following description sounds interesting to
you.
Best regards
Adrian
Here we go:
Corrupt users and compromised user-accounts, cf Phishing, account for the
majority of attacks in the commercial world. We present a two-stage
anti-corruption system (ACS) for database servers that, in the first stage,
makes it very hard to gain access to a database with an unauthorised client
application or from an unauthorised client PC and, in the second stage,
provides
early precise hints on users performing suspicious activities. Our first
implementation of the ACS is for the MS SQL Server 2000. It uses only
documented
functions and relies completely on mechanisms and data already provided by
the
database server. Its smooth and efficient operation for several months in a
middle-sized decentralised company proves it a valuable addition to the pool
of
security measures.
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:5D786B85-382F-49DD-8CE8-3B8A77DB8EF4@.microsoft.com...[vbcol=seagreen]
> Warren,
> Thanks for the info. That does sound like a good solution. I'm assuming
> this
> would allow users to run Enterprise Manager and see my databases and
> objects
> listed, but not interact with them.
> BTW, these applications are web apps using .net. So, perhaps another
> alternative would be for me to autherize and authenticate users to the web
> site using IIS and ADS groups, then use a SQL login and password
> (encrypted
> in the web.config file) to handle db access. Adding your suggestion to
> this,
> I could restrict the SQL login to execute only the needed sprocs.
> Seems like this would prevent any access to my database by those ADS users
> via Enterprise Manager or whatever. If the SQL login should be hacked, at
> least it would only allow execute on the sprocs and nothing else.
> Thanks,
> -Tony
>
> "Warren Brunk" wrote:
>