Sql Server Alignment problem: via

Showing posts with label via. Show all posts

Sunday, March 25, 2012

Altering a connection manager dynamically via a variable

Within an SSIS Package, we are trying to change the connection string of an output file connection manager at runtime (used for package logging).

To do this, we have defined variables with package-level scope and set the connection manager connection string to this variable. The first step of the package is to set these variables. The second step begins the rest of the package operations (moving data). The package executes successfully, but the log file is never created/appended to.

When the package is debugged, I can verify that the variables are being set correctly in the script task and that the variable values are being passed to the connection manager data sources.

Any ideas why this isn’t working?

You shouldn't use script tasks to try and change connection manager connection strings. Use this technique: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

-Jamie

|||Use the mthods in Jamie link that is how I do mine and it works great.

Alter Table!

Hi All, If I add a column to a table how do I ensure its position? Through
the GUI via Ent. Mgr I can drag to the right place, is there a way to do
code wise?
TIAhttp://www.aspfaq.com/2528
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23AgGKg5cGHA.1792@.TK2MSFTNGP03.phx.gbl...
> Hi All, If I add a column to a table how do I ensure its position? Through
> the GUI via Ent. Mgr I can drag to the right place, is there a way to do
> code wise?
> TIA
>|||Without dropping and recreating the table, NO.
This is what EM does in the background.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23AgGKg5cGHA.1792@.TK2MSFTNGP03.phx.gbl...
> Hi All, If I add a column to a table how do I ensure its position? Through
> the GUI via Ent. Mgr I can drag to the right place, is there a way to do
> code wise?
> TIA
>|||A 'position' in the table is irrelevant. You can select the columns in any
order you want. The only time a column order might matter is if you do
'select *' which is not recommended.
EM allows you to put a column in a particular 'position' by completely
recreating the entire table. This can be very inefficient if the table has
lots of data, plus indexes constraints and triggers will all have to be
rebuilt.
If you want the same result in code, you have to do a complete re-creation
of the table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23AgGKg5cGHA.1792@.TK2MSFTNGP03.phx.gbl...
> Hi All, If I add a column to a table how do I ensure its position? Through
> the GUI via Ent. Mgr I can drag to the right place, is there a way to do
> code wise?
> TIA
>sql

Thursday, March 22, 2012

Alter table via t-sql vs Ent Manager

Hello all!
What is the difference between running a alter table statement that adds a
new column to a sql server 2000 table using Query Analyser or Enterprise
Manager?
I noticed that if we do it using Ent. Manager it creates a big script that
copies the whole table to a temp table, adds the new column and than
renames it to the original table dropping the old one.
If we do it using QA we just need a "ALTER TABLE X ADD ..."
Question IS:
Is EM preferable, more reliable?
Is QA safe in the case of a server failure while running the query. What is
the fastest/reliable method if I want to add a column to a 13 milion record
table?. BOL states that alter table statements are logged a fully
recoverable. This recovery would be automatic or would require any manual
statements?
Very confused as you see...
TIA
midHi.
Difference:
If you do change in table structure using EM then:-
1. Unload the data
2. Drop the table and dependants (indexes)
3. Create the table with new definition
4. Create dependant objects
5. Loads back the data and delete the file.
If You do it Query Analyzer using ALTER TABLE then:-
1. It just directly changes the definition or adds a new column.
So it is allways advisible to change the table definition using Query
ANalyzer -- ALTER TABLE command. Since this activity is
logged we can revert back if the database is set in FULL recovery model.
As well as it is very fast, only disadvantage is using alter Table you cann
add a new column only at the last.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> Hello all!
> What is the difference between running a alter table statement that adds a
> new column to a sql server 2000 table using Query Analyser or Enterprise
> Manager?
> I noticed that if we do it using Ent. Manager it creates a big script that
> copies the whole table to a temp table, adds the new column and than
> renames it to the original table dropping the old one.
> If we do it using QA we just need a "ALTER TABLE X ADD ..."
> Question IS:
> Is EM preferable, more reliable?
> Is QA safe in the case of a server failure while running the query. What
is
> the fastest/reliable method if I want to add a column to a 13 milion
record
> table?. BOL states that alter table statements are logged a fully
> recoverable. This recovery would be automatic or would require any manual
> statements?
> Very confused as you see...
> TIA
> mid|||Thanks Hari.
My database is in Simple recovery model. Supose I am running a long alter
table statement via QA and sql server stops. When I bring SQL online again
will the structure of the table reflect the old structure? Will I have a
corrupted table?
When you say "we can revert back if the database is set in FULL recovery
model" I think you mean that if needed we can have the old structure back
again by aplying the last Transaction Log backup. Am I correct?
Thanks again,
mid
On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
> Hi.
> Difference:
> If you do change in table structure using EM then:-
> 1. Unload the data
> 2. Drop the table and dependants (indexes)
> 3. Create the table with new definition
> 4. Create dependant objects
> 5. Loads back the data and delete the file.
>
> If You do it Query Analyzer using ALTER TABLE then:-
> 1. It just directly changes the definition or adds a new column.
>
> So it is allways advisible to change the table definition using Query
> ANalyzer -- ALTER TABLE command. Since this activity is
> logged we can revert back if the database is set in FULL recovery model.
> As well as it is very fast, only disadvantage is using alter Table you cann
> add a new column only at the last.
> Thanks
> Hari
> MCDBA
>
>
>
> "mid" <midbarsinai@.midbarnospam.org> wrote in message
> news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
>> Hello all!
>> What is the difference between running a alter table statement that adds a
>> new column to a sql server 2000 table using Query Analyser or Enterprise
>> Manager?
>> I noticed that if we do it using Ent. Manager it creates a big script that
>> copies the whole table to a temp table, adds the new column and than
>> renames it to the original table dropping the old one.
>> If we do it using QA we just need a "ALTER TABLE X ADD ..."
>> Question IS:
>> Is EM preferable, more reliable?
>> Is QA safe in the case of a server failure while running the query. What
> is
>> the fastest/reliable method if I want to add a column to a 13 milion
> record
>> table?. BOL states that alter table statements are logged a fully
>> recoverable. This recovery would be automatic or would require any manual
>> statements?
>> Very confused as you see...
>> TIA
>> mid|||Hi,
Restarting while doing a ALTER TABLE (Any DDL statement) is bit risky. The
table might not get corrupted rather the change
will be rolled back once the SQL Server comes back. So you can get the old
image of table.
This scenario will be same for SIMPLE or FULL recovery model. In FULL
recovery model even if the ALTER TABLE succeedes you can roll back
to old shape.
But doing this is very risky.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:3ye70mka23ys$.dlg@.midbarnospam.org...
> Thanks Hari.
> My database is in Simple recovery model. Supose I am running a long alter
> table statement via QA and sql server stops. When I bring SQL online again
> will the structure of the table reflect the old structure? Will I have a
> corrupted table?
> When you say "we can revert back if the database is set in FULL recovery
> model" I think you mean that if needed we can have the old structure back
> again by aplying the last Transaction Log backup. Am I correct?
> Thanks again,
> mid
> On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
> > Hi.
> >
> > Difference:
> >
> > If you do change in table structure using EM then:-
> >
> > 1. Unload the data
> > 2. Drop the table and dependants (indexes)
> > 3. Create the table with new definition
> > 4. Create dependant objects
> > 5. Loads back the data and delete the file.
> >
> >
> > If You do it Query Analyzer using ALTER TABLE then:-
> >
> > 1. It just directly changes the definition or adds a new column.
> >
> >
> > So it is allways advisible to change the table definition using Query
> > ANalyzer -- ALTER TABLE command. Since this activity is
> > logged we can revert back if the database is set in FULL recovery model.
> > As well as it is very fast, only disadvantage is using alter Table you
cann
> > add a new column only at the last.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "mid" <midbarsinai@.midbarnospam.org> wrote in message
> > news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> >> Hello all!
> >>
> >> What is the difference between running a alter table statement that
adds a
> >> new column to a sql server 2000 table using Query Analyser or
Enterprise
> >> Manager?
> >>
> >> I noticed that if we do it using Ent. Manager it creates a big script
that
> >> copies the whole table to a temp table, adds the new column and than
> >> renames it to the original table dropping the old one.
> >>
> >> If we do it using QA we just need a "ALTER TABLE X ADD ..."
> >>
> >> Question IS:
> >> Is EM preferable, more reliable?
> >> Is QA safe in the case of a server failure while running the query.
What
> > is
> >> the fastest/reliable method if I want to add a column to a 13 milion
> > record
> >> table?. BOL states that alter table statements are logged a fully
> >> recoverable. This recovery would be automatic or would require any
manual
> >> statements?
> >>
> >> Very confused as you see...
> >>
> >> TIA
> >> mid

Alter table via t-sql vs Ent Manager

Monday, March 19, 2012

Alter Table Add field via JDBC, preparedStatement and Parameter fa

I want to add a field to an existing MS-SQL-2000-table via Microsoft-JDBC-SP3-driver, Version 2.2.0040.
I tried to do this with a preparedStatement object for alter table in Java and wanted to pass the fieldname and fieldtype via Parameter. Then I get the message: cannot find the datatype @.P2 (which seems to be the internal placeholder for params). What is
the mistake or is it not possible to use the alter table command with prepared statement and parameters ?
Would be great, if someone knows something about it.
Here is something of the non working code:
PreparedStatement pstmtM1;
String sqlParamM1 = "ALTER TABLE LIMESTAB ADD [ ? ] [ ? ]";
...
pstmtM1 = conM.prepareStatement(sqlParamM1);
pstmtM1.setString (1, "orderno");
pstmtM1.setString(2,"varchar");
pstmtM1.executeUpdate();
Thank's !!
| Thread-Topic: Alter Table Add field via JDBC, preparedStatement and
Parameter fa
| thread-index: AcR0xrMPXWJxd53zRn66DN+jgi9f0g==
| X-WBNR-Posting-Host: 217.146.157.251
| From: "=?Utf-8?B?ZGJpbmZvcm1hdA==?="
<dbinformat@.discussions.microsoft.com>
| Subject: Alter Table Add field via JDBC, preparedStatement and Parameter
fa
| Date: Wed, 28 Jul 2004 10:17:02 -0700
| Lines: 15
| Message-ID: <19D18530-C3A9-4BFE-82C3-B742C37ED580@.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.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6211
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I want to add a field to an existing MS-SQL-2000-table via
Microsoft-JDBC-SP3-driver, Version 2.2.0040.
| I tried to do this with a preparedStatement object for alter table in
Java and wanted to pass the fieldname and fieldtype via Parameter. Then I
get the message: cannot find the datatype @.P2 (which seems to be the
internal placeholder for params). What is the mistake or is it not possible
to use the alter table command with prepared statement and parameters ?
| Would be great, if someone knows something about it.
| Here is something of the non working code:
|
| PreparedStatement pstmtM1;
| String sqlParamM1 = "ALTER TABLE LIMESTAB ADD [ ? ] [ ? ]";
| ...
| pstmtM1 = conM.prepareStatement(sqlParamM1);
| pstmtM1.setString (1, "orderno");
| pstmtM1.setString(2,"varchar");
| pstmtM1.executeUpdate();
|
| Thank's !!
|
|
Hi,
You cannot submit an ALTER TABLE statement using parameters like this.
Below is how SQL Server is interpreting your code:
exec sp_executesql N'ALTER TABLE LIMESTAB ADD [ @.P1 ] [ @.P2 ]', N'@.P1
nvarchar(4000) ,@.P2 nvarchar(4000) ', N'orderno', N'varchar'
Even in straight T-SQL, you must dynamically build the query string and
then execute it using either sp_executesql or EXECUTE. Since you are using
Java, you should just build your string in the code and then execute it
using a standard Statement object:
Statement stmt = conn.createStatement();
String colname = "orderno";
String coltype = "varchar";
String sql = "ALTER TABLE LIMESTAB ADD ";
stmt.executeUpdate(sql + " " + colname + " " + coltype);
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Sunday, March 11, 2012

Alter table

Hi to all!
I have a silly problem, but I'm not able to solve it!
I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command.
...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance)
So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position?
...Thanks a lot!
Sergio

p.s.: Sorry for my poor english..I hope you understand my question!Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.|||Originally posted by HanafiH
Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.

THANKS A LOT! I SOLVED MY PROBLEM!..

I never take care about icons .........bur from NOW I will consider IT!
Once again thank you!
Sergio

Wednesday, March 7, 2012

alter column with PK / index

I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor
.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
if I have to drop dependencies, can you assist with some code to detect and
build up the indexes again as I will have to do this afterwards.
many thanks.Hi
If this was in source code control your task would be a lot simpler!
Assuming that your PKs/FKs/Indexes are always the same then you could script
them and drop/apply then en-mass or tailor the scripts to do less work. As
you know, this may prolong the process and it is less likely to cope with an
y
anonomalies that may occur.
If you want to do less work start by looking at the sysindexkeys table
and/or INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE views
John
"sysbox27" wrote:

> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> if I have to drop dependencies, can you assist with some code to detect an
d
> build up the indexes again as I will have to do this afterwards.
> many thanks.|||maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:

> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> if I have to drop dependencies, can you assist with some code to detect an
d
> build up the indexes again as I will have to do this afterwards.
> many thanks.

alter column that has pk/index

Hi,
I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
many thanks.
maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:

> Hi,
> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a cursor.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to have
> to drop and recreate indexes due to time involved in rebuilding.
> many thanks.

alter column that has pk/index

Hi,
I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
many thanks.maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:
> Hi,
> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a cursor.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to have
> to drop and recreate indexes due to time involved in rebuilding.
> many thanks.

alter column that has pk/index

Hi,
I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor
.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
many thanks.maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:

> Hi,
> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> many thanks.

Thursday, February 16, 2012

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...
> 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!!
>|||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...
> 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...
> > 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!!
> >
> >
>|||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=djq&as_ugroup=microsoft.public.sqlserver
"Zeng" <zzy@.nonospam.com> wrote in message
news:OsHN6CT4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> 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...
> > 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...
> > > 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!!
> > >
> > >
> >
> >
>|||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...
> 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!!
>|||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...
> 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...
> > 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!!
> >
> >
>

allow user to only read data including via store procedure

"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]
>

Allow SQL Only to be used on IP Address 127.0.0.1

Hello,
I have a webserver with SQL and only want it accessable via the 127.0.0.1 IP
address. How do I do this.
Thanks,
Jack
You could use a firewall:
http://www.kerio.com/kpf_home.html
blocking all connections incoming from addresses different from 127.0.0.1
Henri
"jack" <jack@.mrolinux.com> a crit dans le message de
news:%2379%23H%23ZUEHA.644@.tk2msftngp13.phx.gbl...
> Hello,
> I have a webserver with SQL and only want it accessable via the 127.0.0.1
IP
> address. How do I do this.
> Thanks,
> Jack
>
>
|||"Henri" <hmfireball@.hotmail.com> wrote in message
news:OtdQ$daUEHA.3016@.tk2msftngp13.phx.gbl...
> You could use a firewall:
> http://www.kerio.com/kpf_home.html
> blocking all connections incoming from addresses different from 127.0.0.1
> Henri
You don't need a third party product to do this.
Just go to network connections, and the properties of the ip interface and
set up ip filtering on that ip address.
David
|||Open the SQL Server Network Utility on the webserver
(Start>Run>svrnetcn.exe) , highlight the enabled protocols and click the
disable button. Now the server will only accept local connections. You need
to specify the server as servername or (local) or . rather than the IP
address but the effect is that only local connections are allowed.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"jack" <jack@.mrolinux.com> wrote in message
news:%2379%23H%23ZUEHA.644@.tk2msftngp13.phx.gbl...
> Hello,
> I have a webserver with SQL and only want it accessable via the 127.0.0.1
IP
> address. How do I do this.
> Thanks,
> Jack
>

Allow SQL Only to be used on IP Address 127.0.0.1

Hello,
I have a webserver with SQL and only want it accessable via the 127.0.0.1 IP
address. How do I do this.
Thanks,
JackYou could use a firewall:
http://www.kerio.com/kpf_home.html
blocking all connections incoming from addresses different from 127.0.0.1
Henri
"jack" <jack@.mrolinux.com> a écrit dans le message de
news:%2379%23H%23ZUEHA.644@.tk2msftngp13.phx.gbl...
> Hello,
> I have a webserver with SQL and only want it accessable via the 127.0.0.1
IP
> address. How do I do this.
> Thanks,
> Jack
>
>|||"Henri" <hmfireball@.hotmail.com> wrote in message
news:OtdQ$daUEHA.3016@.tk2msftngp13.phx.gbl...
> You could use a firewall:
> http://www.kerio.com/kpf_home.html
> blocking all connections incoming from addresses different from 127.0.0.1
> Henri
You don't need a third party product to do this.
Just go to network connections, and the properties of the ip interface and
set up ip filtering on that ip address.
David|||Open the SQL Server Network Utility on the webserver
(Start>Run>svrnetcn.exe) , highlight the enabled protocols and click the
disable button. Now the server will only accept local connections. You need
to specify the server as servername or (local) or . rather than the IP
address but the effect is that only local connections are allowed.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"jack" <jack@.mrolinux.com> wrote in message
news:%2379%23H%23ZUEHA.644@.tk2msftngp13.phx.gbl...
> Hello,
> I have a webserver with SQL and only want it accessable via the 127.0.0.1
IP
> address. How do I do this.
> Thanks,
> Jack
>

Allow SQL Only to be used on IP Address 127.0.0.1

Hello,
I have a webserver with SQL and only want it accessable via the 127.0.0.1 IP
address. How do I do this.
Thanks,
JackYou could use a firewall:
http://www.kerio.com/kpf_home.html
blocking all connections incoming from addresses different from 127.0.0.1
Henri
"jack" <jack@.mrolinux.com> a crit dans le message de
news:%2379%23H%23ZUEHA.644@.tk2msftngp13.phx.gbl...
> Hello,
> I have a webserver with SQL and only want it accessable via the 127.0.0.1
IP
> address. How do I do this.
> Thanks,
> Jack
>
>|||"Henri" <hmfireball@.hotmail.com> wrote in message
news:OtdQ$daUEHA.3016@.tk2msftngp13.phx.gbl...
> You could use a firewall:
> http://www.kerio.com/kpf_home.html
> blocking all connections incoming from addresses different from 127.0.0.1
> Henri
You don't need a third party product to do this.
Just go to network connections, and the properties of the ip interface and
set up ip filtering on that ip address.
David|||Open the SQL Server Network Utility on the webserver
(Start>Run>svrnetcn.exe) , highlight the enabled protocols and click the
disable button. Now the server will only accept local connections. You need
to specify the server as servername or (local) or . rather than the IP
address but the effect is that only local connections are allowed.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"jack" <jack@.mrolinux.com> wrote in message
news:%2379%23H%23ZUEHA.644@.tk2msftngp13.phx.gbl...
> Hello,
> I have a webserver with SQL and only want it accessable via the 127.0.0.1
IP
> address. How do I do this.
> Thanks,
> Jack
>

Sunday, March 25, 2012

Altering a connection manager dynamically via a variable

Alter Table!

Thursday, March 22, 2012

Alter table via t-sql vs Ent Manager

Alter table via t-sql vs Ent Manager

Alter table via t-sql vs Ent Manager

Monday, March 19, 2012

Alter Table Add field via JDBC, preparedStatement and Parameter fa

Sunday, March 11, 2012

Alter table

Wednesday, March 7, 2012

alter column with PK / index

alter column that has pk/index

alter column that has pk/index

alter column that has pk/index

Thursday, February 16, 2012

allow user to only read data including via store procedure

allow user to only read data including via store procedure

Allow SQL Only to be used on IP Address 127.0.0.1

Allow SQL Only to be used on IP Address 127.0.0.1

Allow SQL Only to be used on IP Address 127.0.0.1

Sql Server Alignment problem

Blog Archive

About Me