Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find that
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and click
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [columnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but this
is approach is not optimal.
So Much - Yet - So Little
Try running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the bottom.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little
|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>
|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>
|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...[vbcol=seagreen]
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>
|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Alter Table - Add Colum - Ordinal Position?
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==
>X-WBNR-Posting-Host: 207.59.213.130
>From: "=?Utf-8?B?U3dpbmdWb3Rlcg==?=" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position?
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of[vbcol=seagreen]
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
I[vbcol=seagreen]
droops[vbcol=seagreen]
statements[vbcol=seagreen]
Table.[vbcol=seagreen]
rights.[vbcol=seagreen]
find[vbcol=seagreen]
SQL[vbcol=seagreen]
syntax[vbcol=seagreen]
table.[vbcol=seagreen]
you[vbcol=seagreen]
and[vbcol=seagreen]
Is[vbcol=seagreen]
example, I[vbcol=seagreen]
and[vbcol=seagreen]
bottom.[vbcol=seagreen]
but
>

Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find tha
t
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and clic
k
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [colu
mnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but thi
s
is approach is not optimal.
--
So Much - Yet - So LittleTry running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [co
lumnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the botto
m.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...[vbcol=seagreen]
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
>|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Alter Table - Add Colum - Ordinal Position'
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==
>X-WBNR-Posting-Host: 207.59.213.130
>From: "examnotes" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position'
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
>
I[vbcol=seagreen]
droops[vbcol=seagreen]
statements[vbcol=seagreen]
Table.[vbcol=seagreen]
rights.[vbcol=seagreen]
find[vbcol=seagreen]
SQL[vbcol=seagreen]
syntax[vbcol=seagreen]
table.[vbcol=seagreen]
you[vbcol=seagreen]
and[vbcol=seagreen]
Is[vbcol=seagreen]
example, I[vbcol=seagreen]
and[vbcol=seagreen]
bottom.[vbcol=seagreen]
but[vbcol=seagreen]
>

Alphabetical Order - Eliminating the "THE"

Hi,
This may seem like a simple problem, and I'm somewhat embarrassed that
I've been developing for 7 years and haven't been asked to deal with
this - but when you are ordering a list alphabetically, HOW do you
factor out the preceeding "The" in your list items when you do your
ordering. For example in a list of movies such as:
Into the Blue
Madagascar
Spaceballs
The 40-year old virgin
The Exorcism of Emily Rose
doing a standard Order By on the title field would render the list like
that, but according to the rules of english this is an incorrect
ordering, since The 40-year old virgin and The King and I are both
ordered at the end and should instead be ordered at the top. How do
you get this to happen? Here is some generic sample code that would
order the list the way it appears above. How can I modify the code to
order it according to the rules of English.
SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
Thanks in advance.You will need to order on an expression. For example, try the following:
select
*
from
movies
order by
case when left(title,4) = 'The ' then substring(title,5,255) else Title
end
<rwilson290@.hotmail.com> wrote in message
news:1129302125.799285.113600@.g49g2000cwa.googlegroups.com...
> Hi,
> This may seem like a simple problem, and I'm somewhat embarrassed that
> I've been developing for 7 years and haven't been asked to deal with
> this - but when you are ordering a list alphabetically, HOW do you
> factor out the preceeding "The" in your list items when you do your
> ordering. For example in a list of movies such as:
> Into the Blue
> Madagascar
> Spaceballs
> The 40-year old virgin
> The Exorcism of Emily Rose
>
> doing a standard Order By on the title field would render the list like
> that, but according to the rules of english this is an incorrect
> ordering, since The 40-year old virgin and The King and I are both
> ordered at the end and should instead be ordered at the top. How do
> you get this to happen? Here is some generic sample code that would
> order the list the way it appears above. How can I modify the code to
> order it according to the rules of English.
> SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
> Thanks in advance.
>|||I don't really think that it's an English rule for ordering, but I could be
wrong.
For "special" ordering like this, I would add an "order_title" column.
The titles would look like this in the column:
Into the Blue
Madagascar
Spaceballs
40-year old virgin (or Forty-year old virgin)
Exorcism of Emily Rose
Establish the rules and when inserting, format the title and insert the
formatted title in that column .
I'm sure that you'll find other exceptions like titles that start with "A
...".
So your query becomes:
SELECT movie_title, producer, cost from tblMovie ORDER BY order_title
You could, like JT has suggested, incorporate this into the ORDER BY clause
or write a function to do this.
But, depending on the size of this table and other rules may may be added,
this could really slow down your query.
It may be better to take the time when inserted to get it right.
<rwilson290@.hotmail.com> wrote in message
news:1129302125.799285.113600@.g49g2000cwa.googlegroups.com...
> Hi,
> This may seem like a simple problem, and I'm somewhat embarrassed that
> I've been developing for 7 years and haven't been asked to deal with
> this - but when you are ordering a list alphabetically, HOW do you
> factor out the preceeding "The" in your list items when you do your
> ordering. For example in a list of movies such as:
> Into the Blue
> Madagascar
> Spaceballs
> The 40-year old virgin
> The Exorcism of Emily Rose
>
> doing a standard Order By on the title field would render the list like
> that, but according to the rules of english this is an incorrect
> ordering, since The 40-year old virgin and The King and I are both
> ordered at the end and should instead be ordered at the top. How do
> you get this to happen? Here is some generic sample code that would
> order the list the way it appears above. How can I modify the code to
> order it according to the rules of English.
> SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
> Thanks in advance.
>

allow user to only read data including via store procedure

Hello,
I need your advice, I want to create a user that should not be able to cause
any data change to the database. How do I do that?
I have tried to put these on the user:
- public: ON
- Db_denydatawriter: ON
- Db_datareader: ON
With those settings, the user cannot execute any store procedures even
though the store procs only read data. If I add
- Db_owner: ON
...then the user will be able to excute store procedures that also modify
data!
I know that I can go to individual database and overwrite the setting for
each store proc but store procs are changed often, some of them are hugh -
it's not easy to control which of them don't modify data.
Have you run into this problem before? Please help!! Thanks!!You have to be a bit more dilligent in your security. There is no magic
wand that you can use to do this for you. Create a group, let say
read_only_users. Then simply grant execute rights on all procedures that
only read data. Then add the user to this group (only if you want to keep
them to only read access via procedures.)
Louis
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored
"Zeng" <zzy@.nonospam.com> wrote in message
news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> I need your advice, I want to create a user that should not be able to

---

cause

quote:

---

> any data change to the database. How do I do that?
> I have tried to put these on the user:
> - public: ON
> - Db_denydatawriter: ON
> - Db_datareader: ON
> With those settings, the user cannot execute any store procedures even
> though the store procs only read data. If I add
> - Db_owner: ON
> ...then the user will be able to excute store procedures that also modify
> data!
> I know that I can go to individual database and overwrite the setting for
> each store proc but store procs are changed often, some of them are hugh -
> it's not easy to control which of them don't modify data.
> Have you run into this problem before? Please help!! Thanks!!
>

---

|||thanks for the response. Keeping track of store procedures' write and read
operations is not "simple" task, therefore separating them out to grant the
execute rights appropriately is not simple. It's very logical to design a
system that guard datareading and datawriting at the data level (to create
only one gate to guard) - if magic is needed for that - then everybody would
have to go test every other way that can modify or read data even after they
select Db_denydatawriter or Db_denydatareader option. For example, from
what I understand user functions are recently introduced in Sql Server, it's
buggy (but that's not the point), and because it can read and write data,
does that mean I have to go through each function to determine if they are
read-only and keeping track of them too? What if a store proc or a user
function starts out as a read-only and later got changed to include an
update and user forget to move it from one security group to another
group....
It's hard to believe....what I learn here...I'm relatively a newbie with
Sql Server, but the more I learn about it especially when I start doing
replication, the more it appears to me as a dinosaur
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23VGlOXS4DHA.1804@.TK2MSFTNGP12.phx.gbl...

quote:

---

> You have to be a bit more dilligent in your security. There is no magic
> wand that you can use to do this for you. Create a group, let say
> read_only_users. Then simply grant execute rights on all procedures that
> only read data. Then add the user to this group (only if you want to keep
> them to only read access via procedures.)
> Louis
> --
> ----

---

--

quote:

---

> --
> Louis Davidson (drsql@.hotmail.com)
> Compass Technology Management
> Pro SQL Server 2000 Database Design
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are
> interested in consulting services. All other replies will be ignored
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...
> cause
modify[QUOTE]
for[QUOTE]
hugh -[QUOTE]
>

---

|||One thing to factor is that the ability for a user to be able to perform the
operations inside a stored procedure without having direct access to the
underlying objects is actually a security feature. This way, you can lock
down direct access to the objects and only grant the users EXEC permissions
to the stored procedures. I know it doesn't help you in your current
situation, I just want to give a perspective of the design. To be honest, I
think that this is the first case where I've seen this particular request,
but I'm sure that it is sensible in your environment. I can't come up with a
way to "mask" the modification permissions for the modifications that a
stored procedure performs when you grant the user EXEC permissions to the
stored procedure. You could roll your own, of course; have some code in the
proc which checks against a permissions table, but that might not be
feasible to you.
You might want to post this to sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Zeng" <zzy@.nonospam.com> wrote in message
news:OsHN6CT4DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> thanks for the response. Keeping track of store procedures' write and

---

read

quote:

---

> operations is not "simple" task, therefore separating them out to grant

---

the

quote:

---

> execute rights appropriately is not simple. It's very logical to design a
> system that guard datareading and datawriting at the data level (to create
> only one gate to guard) - if magic is needed for that - then everybody

---

would

quote:

---

> have to go test every other way that can modify or read data even after

---

they

quote:

---

> select Db_denydatawriter or Db_denydatareader option. For example, from
> what I understand user functions are recently introduced in Sql Server,

---

it's

quote:

---

> buggy (but that's not the point), and because it can read and write data,
> does that mean I have to go through each function to determine if they are
> read-only and keeping track of them too? What if a store proc or a user
> function starts out as a read-only and later got changed to include an
> update and user forget to move it from one security group to another
> group....
> It's hard to believe....what I learn here...I'm relatively a newbie with
> Sql Server, but the more I learn about it especially when I start doing
> replication, the more it appears to me as a dinosaur
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23VGlOXS4DHA.1804@.TK2MSFTNGP12.phx.gbl...
that[QUOTE]
keep[QUOTE]
> ----
> --
> modify
> for
> hugh -
>

---

|||Hi,
Can you please remove the "ON" from db_denydatawriter role and try executing
the procedure.
Thanks
Hari
MCDBA
"Zeng" <zzy@.nonospam.com> wrote in message
news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> I need your advice, I want to create a user that should not be able to

---

cause

quote:

---

> any data change to the database. How do I do that?
> I have tried to put these on the user:
> - public: ON
> - Db_denydatawriter: ON
> - Db_datareader: ON
> With those settings, the user cannot execute any store procedures even
> though the store procs only read data. If I add
> - Db_owner: ON
> ...then the user will be able to excute store procedures that also modify
> data!
> I know that I can go to individual database and overwrite the setting for
> each store proc but store procs are changed often, some of them are hugh -
> it's not easy to control which of them don't modify data.
> Have you run into this problem before? Please help!! Thanks!!
>

---

|||give the user exec permissions on the stored procedures he needs to be able
to run.
give the user the datareader permissions also, so the user will be able to
read from all objects and have execute permissions on the procedures you
want
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O1jt4ol4DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> Hi,
> Can you please remove the "ON" from db_denydatawriter role and try

---

executing

quote:

---

> the procedure.
> Thanks
> Hari
> MCDBA
>
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...
> cause
modify[QUOTE]
for[QUOTE]
hugh -[QUOTE]
>

---