I am writing a small utility to capture Blocking/Blocked processes that is
running for certiain period of time...say 5 sec or more.
logic that I use in the stored proc is (Current Time Stamp -
sysprocesses.Last_Batch) for the duration of the process.
But I am getting incorrect values!!!
Could some one please let me know How to find the Block Duration ?
Thanks
CheriIf you don't want to reinvent the wheel, have a look at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"cheri" <cheri@.discussions.microsoft.com> wrote in message
news:026B2B6E-7664-427E-8B06-64058AE12DF7@.microsoft.com...
>I am writing a small utility to capture Blocking/Blocked processes that is
> running for certiain period of time...say 5 sec or more.
> logic that I use in the stored proc is (Current Time Stamp -
> sysprocesses.Last_Batch) for the duration of the process.
> But I am getting incorrect values!!!
> Could some one please let me know How to find the Block Duration ?
> Thanks
> Cheri
>|||cheri (cheri@.discussions.microsoft.com) writes:
> I am writing a small utility to capture Blocking/Blocked processes that is
> running for certiain period of time...say 5 sec or more.
> logic that I use in the stored proc is (Current Time Stamp -
> sysprocesses.Last_Batch) for the duration of the process.
> But I am getting incorrect values!!!
> Could some one please let me know How to find the Block Duration ?
I'm not really sure what you mean with "block duration", but if you mean
how the long the process have been blocked, I don't think this value is
available in SQL 2000.
Current Time Stamp - last_batch will give you completely irrelevant values
for idle processes. For running processes, it only tells you how they
have been running the batch, but not for how long they have been blocked.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland !!!
I am looking for 2 thing..
1. Since How long a process is Blocking another process?
2. Since how long a process is blocked...
Any way I can find the same?
"Erland Sommarskog" wrote:
> cheri (cheri@.discussions.microsoft.com) writes:
> I'm not really sure what you mean with "block duration", but if you mean
> how the long the process have been blocked, I don't think this value is
> available in SQL 2000.
> Current Time Stamp - last_batch will give you completely irrelevant values
> for idle processes. For running processes, it only tells you how they
> have been running the batch, but not for how long they have been blocked.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Cheri
You can look at waittime in sysprocesses to see how long a process has been
blocked. Look at waittype to see if the waiting is because of a lock.
--
HTH
Kalen Delaney, SQL Server MVP
"cheri" <cheri@.discussions.microsoft.com> wrote in message
news:B9A14CB8-5637-400D-B76C-6DDC0F9C69B9@.microsoft.com...
> Thanks Erland !!!
> I am looking for 2 thing..
> 1. Since How long a process is Blocking another process?
> 2. Since how long a process is blocked...
> Any way I can find the same?
>
> "Erland Sommarskog" wrote:
>
Wednesday, March 7, 2012
block comment??
Is there a block comment character that I can use in my SQL script files to
comment large sections of SQL code? Something like /* ... */.
Thanks.
-b"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> Is there a block comment character that I can use in my SQL script files
> to
> comment large sections of SQL code? Something like /* ... */.
> Thanks.
> -b
This isn't a joke, is it?
From Books OnLine:
/*...*/ (Comment)
Indicates user-provided text. The text between the /* and */ commenting
characters is not evaluated by the server.|||You have is the right commenting characters /*...*/ there for multi-line
block commenting.
( Also in QA, you can select a large chunk of code & press Ctrl+Shft+C to
comment it & Ctrl+Shift+R to remove it. )
Anith|||That's what I thought it meant too, but when I created a SQL script using
this to comment out a section of code that is not yet fully debugged and the
n
ran the script file, I got several errors from code inside the block comment
.
The specific commands were dealing with foreign key constraints, but they
were within the block comment.
Is that any problem with nesting line comments ( '--') inside a block
comment?
-b
"Raymond D'Anjou" wrote:
> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> This isn't a joke, is it?
> From Books OnLine:
> /*...*/ (Comment)
> Indicates user-provided text. The text between the /* and */ commenting
> characters is not evaluated by the server.
>
>|||I can't see what would cause the problems.
Did you highlight some code before running the script?
Mixing block and line comments does not generate an error (that I know of).
Go alone on a line throws an error:
/*comments
GO
comments*/
Trying to nest block comments doesn't work either:
/*comment
/*comment2*/
comment*/
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>|||Barry
If you just commented out a whole block of code, you maybe have a GO
somewhere in there. This can cause problems.
The following is from Inside SQL Server 2000:
---
GO isn't an SQL command or keyword. It's the end-of-batch signal understood
only by certain client tools. The client interprets it to mean that
everything since the last GO should be sent to the server for execution. SQL
Server never sees the GO command and has no idea what it means. With a
custom application, a batch is executed with a single SQLExecute from ODBC
(or dbsqlexec from DB-Library).
If you include the GO command in the query window, SQL Query Analyzer breaks
up your statement into the indicated batches behind the scenes. Each batch
(as marked by the GO command) is sent individually to SQL Server.
A collection of batches that are frequently executed together is sometimes
called a script. Most of the client tools provide a mechanism for loading a
script that you've saved to a text file and for executing it. In SQL Query
Analyzer, you can use the File/Open command to load a script. From the
command-line OSQL or ISQL programs, we can specify the /i flag followed by a
filename to indicate that the SQL Server batches to execute should come from
the specified file. Alternatively, if we're using OSQL or ISQL
interactively, we can read in a file containing one or more SQL statement by
typing :r followed by the filename. (See SQL Server Books Online for details
about using OSQL and ISQL.)
The fact that the client tool and not SQL Server processes GO can lead to
some unexpected behavior. Suppose you have a script containing several
batches. During testing, you want to comment out a couple of the batches to
ignore them for the time being. Your commented script might look something
like this:
SELECT * FROM authors
/*
GO
SELECT * FROM sales
GO
SELECT * FROM publishers
GO
*/
SELECT * FROM titles
GO
The intention here was to comment out the SELECT from the sales and
publishers tables and to run the SELECT from authors and titles as a single
batch. However, if you run this script from SQL Query Analyzer, you'll get
exactly the opposite behavior! That is, you'll see the data from the sales
and publishers tables but not from authors or titles. If you look at this
script from the perspective of the client tool, the behavior makes sense.
The tool doesn't try to interpret any of your SQL statements; it just breaks
the statements into batches to be sent to SQL Server. A batch is marked by a
GO command at the beginning of a line.
So the above script contains four batches. The first batch (everything
before the first GO) is:
SELECT * FROM authors
/*
SQL Server generates an error message because there's an open comment with
no corresponding close comment.
The second and third batches are:
SELECT * FROM sales
and
SELECT * FROM publishers
Both of these batches are perfectly legal, and SQL Server can process them
and return results.
The fourth batch is:
*/
SELECT * FROM titles
SQL Server also generates an error for this last one because it has a close
comment without an open comment marker, and no data is returned.
If you want to comment out statements within a script that can contain the
end-of-batch GO command, you should use the alternative comment marker-the
double dash-in front of every GO. Alternatively, you can just use the double
dash in front of every line you want to comment out. Your script would then
look like this:
SELECT * FROM authors
-- GO
-- SELECT * FROM sales
-- GO
-- SELECT * FROM publishers
-- GO
SELECT * FROM titles
GO
SQL Query Analyzer makes it easy to comment out a group of lines as in the
code above. You can highlight all the lines to be commented out, and from
the Edit menu, choose Advanced/Comment Out. The keystroke combination to
perform the same action is Ctrl-Shift-C. With this revised script, the
client tool won't recognize the GO as the end-of-batch marker because it's
not the first thing on a line. The client will consider this script to be
one single batch and send it to SQL Server as such.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>
>|||I've verified that there are no improperly commented GO statements. When I
rerun the script the error comes after several other similar calls from
inside the comment block (1034 lines into the block of 1137 lines). Is it
possible that these comment blocks are too long?
"Kalen Delaney" wrote:
> Barry
> If you just commented out a whole block of code, you maybe have a GO
> somewhere in there. This can cause problems.
> The following is from Inside SQL Server 2000:
> ---
> GO isn't an SQL command or keyword. It's the end-of-batch signal understoo
d
> only by certain client tools. The client interprets it to mean that
> everything since the last GO should be sent to the server for execution. S
QL
> Server never sees the GO command and has no idea what it means. With a
> custom application, a batch is executed with a single SQLExecute from ODBC
> (or dbsqlexec from DB-Library).
> If you include the GO command in the query window, SQL Query Analyzer brea
ks
> up your statement into the indicated batches behind the scenes. Each batch
> (as marked by the GO command) is sent individually to SQL Server.
> A collection of batches that are frequently executed together is sometimes
> called a script. Most of the client tools provide a mechanism for loading
a
> script that you've saved to a text file and for executing it. In SQL Query
> Analyzer, you can use the File/Open command to load a script. From the
> command-line OSQL or ISQL programs, we can specify the /i flag followed by
a
> filename to indicate that the SQL Server batches to execute should come fr
om
> the specified file. Alternatively, if we're using OSQL or ISQL
> interactively, we can read in a file containing one or more SQL statement
by
> typing :r followed by the filename. (See SQL Server Books Online for detai
ls
> about using OSQL and ISQL.)
> The fact that the client tool and not SQL Server processes GO can lead to
> some unexpected behavior. Suppose you have a script containing several
> batches. During testing, you want to comment out a couple of the batches t
o
> ignore them for the time being. Your commented script might look something
> like this:
> SELECT * FROM authors
> /*
> GO
> SELECT * FROM sales
> GO
> SELECT * FROM publishers
> GO
> */
> SELECT * FROM titles
> GO
> The intention here was to comment out the SELECT from the sales and
> publishers tables and to run the SELECT from authors and titles as a singl
e
> batch. However, if you run this script from SQL Query Analyzer, you'll get
> exactly the opposite behavior! That is, you'll see the data from the sales
> and publishers tables but not from authors or titles. If you look at this
> script from the perspective of the client tool, the behavior makes sense.
> The tool doesn't try to interpret any of your SQL statements; it just brea
ks
> the statements into batches to be sent to SQL Server. A batch is marked by
a
> GO command at the beginning of a line.
> So the above script contains four batches. The first batch (everything
> before the first GO) is:
> SELECT * FROM authors
> /*
> SQL Server generates an error message because there's an open comment with
> no corresponding close comment.
> The second and third batches are:
> SELECT * FROM sales
> and
> SELECT * FROM publishers
> Both of these batches are perfectly legal, and SQL Server can process them
> and return results.
> The fourth batch is:
> */
> SELECT * FROM titles
> SQL Server also generates an error for this last one because it has a clos
e
> comment without an open comment marker, and no data is returned.
> If you want to comment out statements within a script that can contain the
> end-of-batch GO command, you should use the alternative comment marker-the
> double dash-in front of every GO. Alternatively, you can just use the doub
le
> dash in front of every line you want to comment out. Your script would the
n
> look like this:
> SELECT * FROM authors
> -- GO
> -- SELECT * FROM sales
> -- GO
> -- SELECT * FROM publishers
> -- GO
> SELECT * FROM titles
> GO
> SQL Query Analyzer makes it easy to comment out a group of lines as in the
> code above. You can highlight all the lines to be commented out, and from
> the Edit menu, choose Advanced/Comment Out. The keystroke combination to
> perform the same action is Ctrl-Shift-C. With this revised script, the
> client tool won't recognize the GO as the end-of-batch marker because it's
> not the first thing on a line. The client will consider this script to be
> one single batch and send it to SQL Server as such.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
>
>|||OK - Here is what I've narrowed it down to.
If I comment out (using block comments) anything in the script except
another '*' character, it seems to work. However when the commented section
includes a '*' it causes the code to uncomment.
For example the following generated an error
/*
-- if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
*/
but changing the '*' to 'id' did not.
I decided to use QA to comment the block with line comments instead.
-b
"Barry" wrote:
> I've verified that there are no improperly commented GO statements. When
I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>|||Barry
This seems very strange.
This batch works for me:
USE pubs
select * from authors
/*
select * from titles
*/
select * from publishers
What exact version are you using? Are you getting the error using QA?
Thanks
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:846D3E3A-34D8-47EF-A22D-CAC114D7CCB8@.microsoft.com...
> OK - Here is what I've narrowed it down to.
> If I comment out (using block comments) anything in the script except
> another '*' character, it seems to work. However when the commented
> section
> includes a '*' it causes the code to uncomment.
> For example the following generated an error
> /*
> -- if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and
> OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> */
> but changing the '*' to 'id' did not.
> I decided to use QA to comment the block with line comments instead.
> -b
> "Barry" wrote:
>
>|||Did you copy this SQL into notepad (or another 3rd party editor) and then
paste back into Enterprise Manager or Query Analyzer? There is a very rare
(but insideous) bug at least in SQL Server 2000 where a line ending in a
CRTL byte but no LF byte (or perhaps vice versa) will look normal when
viewed in QA or EM, but when executed, the SQL interpreter will think there
is no new line.
For example:
Here is what it looks like when viewed in Enterprise Manager:
-- Here is a comment
print 'Hello World'
Here is what gets executed:
-- Here is a comment print 'Hello World'
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:145BDC63-89E3-4979-9377-29199B3A10EB@.microsoft.com...
> I've verified that there are no improperly commented GO statements. When
> I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>
comment large sections of SQL code? Something like /* ... */.
Thanks.
-b"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> Is there a block comment character that I can use in my SQL script files
> to
> comment large sections of SQL code? Something like /* ... */.
> Thanks.
> -b
This isn't a joke, is it?
From Books OnLine:
/*...*/ (Comment)
Indicates user-provided text. The text between the /* and */ commenting
characters is not evaluated by the server.|||You have is the right commenting characters /*...*/ there for multi-line
block commenting.
( Also in QA, you can select a large chunk of code & press Ctrl+Shft+C to
comment it & Ctrl+Shift+R to remove it. )
Anith|||That's what I thought it meant too, but when I created a SQL script using
this to comment out a section of code that is not yet fully debugged and the
n
ran the script file, I got several errors from code inside the block comment
.
The specific commands were dealing with foreign key constraints, but they
were within the block comment.
Is that any problem with nesting line comments ( '--') inside a block
comment?
-b
"Raymond D'Anjou" wrote:
> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> This isn't a joke, is it?
> From Books OnLine:
> /*...*/ (Comment)
> Indicates user-provided text. The text between the /* and */ commenting
> characters is not evaluated by the server.
>
>|||I can't see what would cause the problems.
Did you highlight some code before running the script?
Mixing block and line comments does not generate an error (that I know of).
Go alone on a line throws an error:
/*comments
GO
comments*/
Trying to nest block comments doesn't work either:
/*comment
/*comment2*/
comment*/
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>|||Barry
If you just commented out a whole block of code, you maybe have a GO
somewhere in there. This can cause problems.
The following is from Inside SQL Server 2000:
---
GO isn't an SQL command or keyword. It's the end-of-batch signal understood
only by certain client tools. The client interprets it to mean that
everything since the last GO should be sent to the server for execution. SQL
Server never sees the GO command and has no idea what it means. With a
custom application, a batch is executed with a single SQLExecute from ODBC
(or dbsqlexec from DB-Library).
If you include the GO command in the query window, SQL Query Analyzer breaks
up your statement into the indicated batches behind the scenes. Each batch
(as marked by the GO command) is sent individually to SQL Server.
A collection of batches that are frequently executed together is sometimes
called a script. Most of the client tools provide a mechanism for loading a
script that you've saved to a text file and for executing it. In SQL Query
Analyzer, you can use the File/Open command to load a script. From the
command-line OSQL or ISQL programs, we can specify the /i flag followed by a
filename to indicate that the SQL Server batches to execute should come from
the specified file. Alternatively, if we're using OSQL or ISQL
interactively, we can read in a file containing one or more SQL statement by
typing :r followed by the filename. (See SQL Server Books Online for details
about using OSQL and ISQL.)
The fact that the client tool and not SQL Server processes GO can lead to
some unexpected behavior. Suppose you have a script containing several
batches. During testing, you want to comment out a couple of the batches to
ignore them for the time being. Your commented script might look something
like this:
SELECT * FROM authors
/*
GO
SELECT * FROM sales
GO
SELECT * FROM publishers
GO
*/
SELECT * FROM titles
GO
The intention here was to comment out the SELECT from the sales and
publishers tables and to run the SELECT from authors and titles as a single
batch. However, if you run this script from SQL Query Analyzer, you'll get
exactly the opposite behavior! That is, you'll see the data from the sales
and publishers tables but not from authors or titles. If you look at this
script from the perspective of the client tool, the behavior makes sense.
The tool doesn't try to interpret any of your SQL statements; it just breaks
the statements into batches to be sent to SQL Server. A batch is marked by a
GO command at the beginning of a line.
So the above script contains four batches. The first batch (everything
before the first GO) is:
SELECT * FROM authors
/*
SQL Server generates an error message because there's an open comment with
no corresponding close comment.
The second and third batches are:
SELECT * FROM sales
and
SELECT * FROM publishers
Both of these batches are perfectly legal, and SQL Server can process them
and return results.
The fourth batch is:
*/
SELECT * FROM titles
SQL Server also generates an error for this last one because it has a close
comment without an open comment marker, and no data is returned.
If you want to comment out statements within a script that can contain the
end-of-batch GO command, you should use the alternative comment marker-the
double dash-in front of every GO. Alternatively, you can just use the double
dash in front of every line you want to comment out. Your script would then
look like this:
SELECT * FROM authors
-- GO
-- SELECT * FROM sales
-- GO
-- SELECT * FROM publishers
-- GO
SELECT * FROM titles
GO
SQL Query Analyzer makes it easy to comment out a group of lines as in the
code above. You can highlight all the lines to be commented out, and from
the Edit menu, choose Advanced/Comment Out. The keystroke combination to
perform the same action is Ctrl-Shift-C. With this revised script, the
client tool won't recognize the GO as the end-of-batch marker because it's
not the first thing on a line. The client will consider this script to be
one single batch and send it to SQL Server as such.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>
>|||I've verified that there are no improperly commented GO statements. When I
rerun the script the error comes after several other similar calls from
inside the comment block (1034 lines into the block of 1137 lines). Is it
possible that these comment blocks are too long?
"Kalen Delaney" wrote:
> Barry
> If you just commented out a whole block of code, you maybe have a GO
> somewhere in there. This can cause problems.
> The following is from Inside SQL Server 2000:
> ---
> GO isn't an SQL command or keyword. It's the end-of-batch signal understoo
d
> only by certain client tools. The client interprets it to mean that
> everything since the last GO should be sent to the server for execution. S
QL
> Server never sees the GO command and has no idea what it means. With a
> custom application, a batch is executed with a single SQLExecute from ODBC
> (or dbsqlexec from DB-Library).
> If you include the GO command in the query window, SQL Query Analyzer brea
ks
> up your statement into the indicated batches behind the scenes. Each batch
> (as marked by the GO command) is sent individually to SQL Server.
> A collection of batches that are frequently executed together is sometimes
> called a script. Most of the client tools provide a mechanism for loading
a
> script that you've saved to a text file and for executing it. In SQL Query
> Analyzer, you can use the File/Open command to load a script. From the
> command-line OSQL or ISQL programs, we can specify the /i flag followed by
a
> filename to indicate that the SQL Server batches to execute should come fr
om
> the specified file. Alternatively, if we're using OSQL or ISQL
> interactively, we can read in a file containing one or more SQL statement
by
> typing :r followed by the filename. (See SQL Server Books Online for detai
ls
> about using OSQL and ISQL.)
> The fact that the client tool and not SQL Server processes GO can lead to
> some unexpected behavior. Suppose you have a script containing several
> batches. During testing, you want to comment out a couple of the batches t
o
> ignore them for the time being. Your commented script might look something
> like this:
> SELECT * FROM authors
> /*
> GO
> SELECT * FROM sales
> GO
> SELECT * FROM publishers
> GO
> */
> SELECT * FROM titles
> GO
> The intention here was to comment out the SELECT from the sales and
> publishers tables and to run the SELECT from authors and titles as a singl
e
> batch. However, if you run this script from SQL Query Analyzer, you'll get
> exactly the opposite behavior! That is, you'll see the data from the sales
> and publishers tables but not from authors or titles. If you look at this
> script from the perspective of the client tool, the behavior makes sense.
> The tool doesn't try to interpret any of your SQL statements; it just brea
ks
> the statements into batches to be sent to SQL Server. A batch is marked by
a
> GO command at the beginning of a line.
> So the above script contains four batches. The first batch (everything
> before the first GO) is:
> SELECT * FROM authors
> /*
> SQL Server generates an error message because there's an open comment with
> no corresponding close comment.
> The second and third batches are:
> SELECT * FROM sales
> and
> SELECT * FROM publishers
> Both of these batches are perfectly legal, and SQL Server can process them
> and return results.
> The fourth batch is:
> */
> SELECT * FROM titles
> SQL Server also generates an error for this last one because it has a clos
e
> comment without an open comment marker, and no data is returned.
> If you want to comment out statements within a script that can contain the
> end-of-batch GO command, you should use the alternative comment marker-the
> double dash-in front of every GO. Alternatively, you can just use the doub
le
> dash in front of every line you want to comment out. Your script would the
n
> look like this:
> SELECT * FROM authors
> -- GO
> -- SELECT * FROM sales
> -- GO
> -- SELECT * FROM publishers
> -- GO
> SELECT * FROM titles
> GO
> SQL Query Analyzer makes it easy to comment out a group of lines as in the
> code above. You can highlight all the lines to be commented out, and from
> the Edit menu, choose Advanced/Comment Out. The keystroke combination to
> perform the same action is Ctrl-Shift-C. With this revised script, the
> client tool won't recognize the GO as the end-of-batch marker because it's
> not the first thing on a line. The client will consider this script to be
> one single batch and send it to SQL Server as such.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
>
>|||OK - Here is what I've narrowed it down to.
If I comment out (using block comments) anything in the script except
another '*' character, it seems to work. However when the commented section
includes a '*' it causes the code to uncomment.
For example the following generated an error
/*
-- if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
*/
but changing the '*' to 'id' did not.
I decided to use QA to comment the block with line comments instead.
-b
"Barry" wrote:
> I've verified that there are no improperly commented GO statements. When
I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>|||Barry
This seems very strange.
This batch works for me:
USE pubs
select * from authors
/*
select * from titles
*/
select * from publishers
What exact version are you using? Are you getting the error using QA?
Thanks
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:846D3E3A-34D8-47EF-A22D-CAC114D7CCB8@.microsoft.com...
> OK - Here is what I've narrowed it down to.
> If I comment out (using block comments) anything in the script except
> another '*' character, it seems to work. However when the commented
> section
> includes a '*' it causes the code to uncomment.
> For example the following generated an error
> /*
> -- if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and
> OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> */
> but changing the '*' to 'id' did not.
> I decided to use QA to comment the block with line comments instead.
> -b
> "Barry" wrote:
>
>|||Did you copy this SQL into notepad (or another 3rd party editor) and then
paste back into Enterprise Manager or Query Analyzer? There is a very rare
(but insideous) bug at least in SQL Server 2000 where a line ending in a
CRTL byte but no LF byte (or perhaps vice versa) will look normal when
viewed in QA or EM, but when executed, the SQL interpreter will think there
is no new line.
For example:
Here is what it looks like when viewed in Enterprise Manager:
-- Here is a comment
print 'Hello World'
Here is what gets executed:
-- Here is a comment print 'Hello World'
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:145BDC63-89E3-4979-9377-29199B3A10EB@.microsoft.com...
> I've verified that there are no improperly commented GO statements. When
> I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>
block all apps except for...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.
|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)
>
> TIA, ChrisR
>
|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com... [vbcol=seagreen]
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
come[vbcol=seagreen]
boss
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>
|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.
|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)
>
> TIA, ChrisR
>
|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com... [vbcol=seagreen]
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
come[vbcol=seagreen]
boss
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>
|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
block all apps except for...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisROn Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)
>
> TIA, ChrisR
>|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com...
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
> >Is there a way to make it so that nobody can connect to sql unless they
come
> >in through a certain app... without the use of Application Roles. ( my
boss
> >doesnt want to have to pass in a password each time.)
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisROn Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)
>
> TIA, ChrisR
>|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com...
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
> >Is there a way to make it so that nobody can connect to sql unless they
come
> >in through a certain app... without the use of Application Roles. ( my
boss
> >doesnt want to have to pass in a password each time.)
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
block a user from executing system stored procs?
How do you block a user from executing system stored procs? Seems a user is
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user
>> is granted access to execute them even when they are not part of any
>> schema or role. The user security page says "default schema > dbo" and
>> owned schemas are all blank, and no role members are checked. So why
>> can the user run system stored procs? I need to block that but don't
>> want to run a "deny" on every system stored proc if I can help it.
>|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help it.
>>
>|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to see
>> all the nice little stored procedures he could call to do damage. That's
>> just one example. I was surprised when someone showed me an example of
>> how a hacker could learn all the stored procs and other object
>> information in a database this way. I can't believe this access is
>> enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>|||> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Running a proc isn't the same as seeing meta-data through the proc.
That is not what I am seeing. How many rows is returned from thelast SELECT in the first section of
above code, for instance?
USE master
CREATE LOGIN TestLogin WITH PASSWORD = 'jdhsjfdjk2'
CREATE DATABASE testdb
GO
USE testdb
CREATE TABLE dbo.t(c1 int)
CREATE USER TestLogin
GRANT EXECUTE TO TestLogin
EXECUTE AS LOGIN = 'TestLogin'
EXEC sp_tables --No "t" table
SELECT * FROM sys.tables WHERE name = 't' --0 rows
GO
--Cleanup
REVERT
USE master
DROP DATABASE testdb
DROP LOGIN TestLogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is protected.
>> A user might be able to run sp_help, but the only objects they could see would be the objects
>> they own, or the ones they were specifically granted access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets compromised, the user can't run
>> things *like* sp_stored_procedures to see all the nice little stored procedures he could call to
>> do damage. That's just one example. I was surprised when someone showed me an example of how
>> a hacker could learn all the stored procs and other object information in a database this way.
>> I can't believe this access is enabled by default and not discussed in security practices
>> articles.
>> There are hundreds of system procs, as you know, and I wouldn't know which ones to manually DENY
>> without a lot of research, and then a lot of one-by-one commands. So, I want to block all the
>> system procs. This user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the Management Studio or
>> Enterprise Manager, so if you denied access to all procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user is granted access to
>> execute them even when they are not part of any schema or role. The user security page says
>> "default schema > dbo" and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but don't want to run a "deny"
>> on every system stored proc if I can help it.
>>
>>
>>
>|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is
>> protected.
>> A user might be able to run sp_help, but the only objects they could see
>> would be the objects they own, or the ones they were specifically granted
>> access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to
>> see all the nice little stored procedures he could call to do damage.
>> That's just one example. I was surprised when someone showed me an
>> example of how a hacker could learn all the stored procs and other
>> object information in a database this way. I can't believe this access
>> is enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema >
>> dbo" and owned schemas are all blank, and no role members are checked.
>> So why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>>
>
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user
>> is granted access to execute them even when they are not part of any
>> schema or role. The user security page says "default schema > dbo" and
>> owned schemas are all blank, and no role members are checked. So why
>> can the user run system stored procs? I need to block that but don't
>> want to run a "deny" on every system stored proc if I can help it.
>|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help it.
>>
>|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to see
>> all the nice little stored procedures he could call to do damage. That's
>> just one example. I was surprised when someone showed me an example of
>> how a hacker could learn all the stored procs and other object
>> information in a database this way. I can't believe this access is
>> enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>|||> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Running a proc isn't the same as seeing meta-data through the proc.
That is not what I am seeing. How many rows is returned from thelast SELECT in the first section of
above code, for instance?
USE master
CREATE LOGIN TestLogin WITH PASSWORD = 'jdhsjfdjk2'
CREATE DATABASE testdb
GO
USE testdb
CREATE TABLE dbo.t(c1 int)
CREATE USER TestLogin
GRANT EXECUTE TO TestLogin
EXECUTE AS LOGIN = 'TestLogin'
EXEC sp_tables --No "t" table
SELECT * FROM sys.tables WHERE name = 't' --0 rows
GO
--Cleanup
REVERT
USE master
DROP DATABASE testdb
DROP LOGIN TestLogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is protected.
>> A user might be able to run sp_help, but the only objects they could see would be the objects
>> they own, or the ones they were specifically granted access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets compromised, the user can't run
>> things *like* sp_stored_procedures to see all the nice little stored procedures he could call to
>> do damage. That's just one example. I was surprised when someone showed me an example of how
>> a hacker could learn all the stored procs and other object information in a database this way.
>> I can't believe this access is enabled by default and not discussed in security practices
>> articles.
>> There are hundreds of system procs, as you know, and I wouldn't know which ones to manually DENY
>> without a lot of research, and then a lot of one-by-one commands. So, I want to block all the
>> system procs. This user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the Management Studio or
>> Enterprise Manager, so if you denied access to all procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user is granted access to
>> execute them even when they are not part of any schema or role. The user security page says
>> "default schema > dbo" and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but don't want to run a "deny"
>> on every system stored proc if I can help it.
>>
>>
>>
>|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is
>> protected.
>> A user might be able to run sp_help, but the only objects they could see
>> would be the objects they own, or the ones they were specifically granted
>> access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to
>> see all the nice little stored procedures he could call to do damage.
>> That's just one example. I was surprised when someone showed me an
>> example of how a hacker could learn all the stored procs and other
>> object information in a database this way. I can't believe this access
>> is enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema >
>> dbo" and owned schemas are all blank, and no role members are checked.
>> So why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>>
>
block a user from executing system stored procs?
How do you block a user from executing system stored procs? Seems a user is
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.
What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>
|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>
|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>
|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>
|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.
What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>
|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>
|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>
|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>
|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>
block a user from executing system stored procs?
How do you block a user from executing system stored procs? Seems a user is
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunne
r.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunne
r.com...
>|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner
.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner
.com...
>|||> This is not consistent with my testing. My test shows that any user can run system stored
procs.
> Running a proc isn't the same as seeing meta-data through the proc.
That is not what I am seeing. How many rows is returned from thelast SELECT
in the first section of
above code, for instance?
USE master
CREATE LOGIN TestLogin WITH PASSWORD = 'jdhsjfdjk2'
CREATE DATABASE testdb
GO
USE testdb
CREATE TABLE dbo.t(c1 int)
CREATE USER TestLogin
GRANT EXECUTE TO TestLogin
EXECUTE AS LOGIN = 'TestLogin'
EXEC sp_tables --No "t" table
SELECT * FROM sys.tables WHERE name = 't' --0 rows
GO
--Cleanup
REVERT
USE master
DROP DATABASE testdb
DROP LOGIN TestLogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner
.com...
> This is not consistent with my testing. My test shows that any user can r
un system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner
.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunne
r.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunne
r.com...
>|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner
.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner
.com...
>|||> This is not consistent with my testing. My test shows that any user can run system stored
procs.
> Running a proc isn't the same as seeing meta-data through the proc.
That is not what I am seeing. How many rows is returned from thelast SELECT
in the first section of
above code, for instance?
USE master
CREATE LOGIN TestLogin WITH PASSWORD = 'jdhsjfdjk2'
CREATE DATABASE testdb
GO
USE testdb
CREATE TABLE dbo.t(c1 int)
CREATE USER TestLogin
GRANT EXECUTE TO TestLogin
EXECUTE AS LOGIN = 'TestLogin'
EXEC sp_tables --No "t" table
SELECT * FROM sys.tables WHERE name = 't' --0 rows
GO
--Cleanup
REVERT
USE master
DROP DATABASE testdb
DROP LOGIN TestLogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner
.com...
> This is not consistent with my testing. My test shows that any user can r
un system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner
.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>
Subscribe to:
Posts (Atom)