2nd REPOST (HELP!): Blocking access to system sprocs

I have posted two other times and Kalen Delaney and David Browne were kind
enough to respond. I thank them. Nonetheless, I am still without a
solution and need help. My first subject probably wasn't good enough and
perhaps I missed the attention of others in this group. This problem is
not as easy of an issue as it sounds. I think this is a serious security
hole in SQL Server and I hope Microsoft jumps into this thread. If you
disagree with me about how serious this is, please just be respectful that I
want a solution for my purposes nonetheless.
System: SQL 2005
Problem: I want to set up a user (from ASP.NET code) that has only execute
access on the database stored procs, but NOT access to system stored procs.
But, from my testing, even before I issue the issue any access or role
membership to a user, it seems that any users of a database can access
system stored procs (such as sp_stored_procedures, which exposes all the
user stored proc names that they can call with their execute access!).
Why is this a security problem, some ask? Because you never want to expose
your table names and proc names should a hacker somehow get your
username/pass. Also, even aside from knowing the company database
metadata, there are some system procs that do real damage, as mentioned in
this video:
http://www.learnsqlserver.com/VideoTutorials/Sql-Server-Video/352/Some-Dangerous-Stored-Procedures-xp-cmdshell-sp.aspx
Some argue that blocking system stored procs can ruin Management Studio
features for that user. True, but that is fine for an ASP.NET user that
will never be in Management Studio.
So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
I've tried that and it doesn't work. You get an internal error about
permissions on server scoped catalog views or system stored procedures.
One not-so-good workaround? This issue has been discussed in only one other
forum I found. The solution was to create a same-named user in MASTER, and
then DENY execute on each individual system stored proc in master. That
sounds like a horribly tedious solution, and I'm not crazy about hand-typing
hundreds of system stored proc names. Besides, you would have to do this by
user. I tried doing it on a role, like a "db_executor" role I created, but
that doesn't work at all. See:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
I also found this Micrsoft article, but it didn't help because sys was not
recognized from inside my database, nor was system_objects:
http://msdn2.microsoft.com/en-us/library/ms178634.aspx
Pleeeeasssse help with a solution to blocking system stored procs, even if
you don't think it is that necessary for security. Thank you.
On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
real.com> wrote:
> I have posted two other times and Kalen Delaney and David Browne were kind
> enough to respond. I thank them. Nonetheless, I am still without a
> solution and need help. My first subject probably wasn't good enough and
> perhaps I missed the attention of others in this group. This problem is
> not as easy of an issue as it sounds. I think this is a serious security
> hole in SQL Server and I hope Microsoft jumps into this thread. If you
> disagree with me about how serious this is, please just be respectful that I
> want a solution for my purposes nonetheless.
> System: SQL 2005
> Problem: I want to set up a user (from ASP.NET code) that has only execute
> access on the database stored procs, but NOT access to system stored procs.
> But, from my testing, even before I issue the issue any access or role
> membership to a user, it seems that any users of a database can access
> system stored procs (such as sp_stored_procedures, which exposes all the
> user stored proc names that they can call with their execute access!).
> Why is this a security problem, some ask? Because you never want to expose
> your table names and proc names should a hacker somehow get your
> username/pass. Also, even aside from knowing the company database
> metadata, there are some system procs that do real damage, as mentioned in
> this video:http://www.learnsqlserver.com/VideoTutorials/Sql-Server-Video/352/Som...
> Some argue that blocking system stored procs can ruin Management Studio
> features for that user. True, but that is fine for an ASP.NET user that
> will never be in Management Studio.
> So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
> I've tried that and it doesn't work. You get an internal error about
> permissions on server scoped catalog views or system stored procedures.
> One not-so-good workaround? This issue has been discussed in only one other
> forum I found. The solution was to create a same-named user in MASTER, and
> then DENY execute on each individual system stored proc in master. That
> sounds like a horribly tedious solution, and I'm not crazy about hand-typing
> hundreds of system stored proc names. Besides, you would have to do this by
> user. I tried doing it on a role, like a "db_executor" role I created, but
> that doesn't work at all. See:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
> I also found this Micrsoft article, but it didn't help because sys was not
> recognized from inside my database, nor was system_objects:http://msdn2.microsoft.com/en-us/library/ms178634.aspx
> Pleeeeasssse help with a solution to blocking system stored procs, even if
> you don't think it is that necessary for security. Thank you.
This is easily done, I just confirmed that I can prevent a standard
SQL login from executing sp_help. Add the login to your user database
AND to the master database. In MASTER, go into the properties for
that login. Under "Securables", add "All objects belonging to the
schema" and choose the "sys" schema. Select the sprocs that you want
to prevent execution of, and check the "Deny" box.
I confirmed this by creating a login named "test", logged in with that
login, ran sp_help. I then denied access as described above, and now
get a permission denied error trying to run sp_help.
|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173294431.368031.226630@.s48g2000cws.googlegr oups.com...
> On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
> real.com> wrote:
> This is easily done, I just confirmed that I can prevent a standard
> SQL login from executing sp_help. Add the login to your user database
> AND to the master database. In MASTER, go into the properties for
> that login. Under "Securables", add "All objects belonging to the
> schema" and choose the "sys" schema. Select the sprocs that you want
> to prevent execution of, and check the "Deny" box.
> I confirmed this by creating a login named "test", logged in with that
> login, ran sp_help. I then denied access as described above, and now
> get a permission denied error trying to run sp_help.
>
Well, I agree that works, thanks, but that is the same concept as in the
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
article which I posted earlier, except yours is through the GUI. The
problem, still, is that I would have to do this for hundreds of stored
procedures, which is a LOT of clicking considering you have to select a proc
in one pane and then choose the deny in the other pane. Doing that would
take a long time for just one user.
I appreciate the reply, but I cannot believe Microsoft has not given us an
easier way to block access to all system stored procs. I suppose they're
waiting for some big company to have this exploit used on them, and then
they'll issue a service pack a year later.
|||On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
wrote:
> Well, I agree that works, thanks, but that is the same concept as in thehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
> article which I posted earlier, except yours is through the GUI. The
> problem, still, is that I would have to do this for hundreds of stored
> procedures, which is a LOT of clicking considering you have to select a proc
> in one pane and then choose the deny in the other pane. Doing that would
> take a long time for just one user.
> I appreciate the reply, but I cannot believe Microsoft has not given us an
> easier way to block access to all system stored procs. I suppose they're
> waiting for some big company to have this exploit used on them, and then
> they'll issue a service pack a year later.
Rather than nitpick about what Microsoft did or did not do, can't you
simply write a script that loops through sysobjects and issues the
necessary DENY's against every stored proc? This query will get you
all of the system procs if you run it in master:
SELECT *
FROM dbo.sysobjects
WHERE xtype = 'P'
I'm betting there's another way to do this using schemas, but I
haven't worked with them enough to know.
|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173357526.661405.239300@.64g2000cwx.googlegro ups.com...
> On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
> wrote:
> Rather than nitpick about what Microsoft did or did not do, can't you
> simply write a script that loops through sysobjects and issues the
> necessary DENY's against every stored proc? This query will get you
> all of the system procs if you run it in master:
> SELECT *
> FROM dbo.sysobjects
> WHERE xtype = 'P'
> I'm betting there's another way to do this using schemas, but I
> haven't worked with them enough to know.
>
I think Microsoft deserves the criticism. This is a security less-than-best
practice that is not easy to work around. Their default setup here is NOT
good. I will try the 1200 DENY statements and see if that messes up
anything. Thanks.

2nd REPOST (HELP!): Blocking access to system sprocs

I have posted two other times and Kalen Delaney and David Browne were kind
enough to respond. I thank them. Nonetheless, I am still without a
solution and need help. My first subject probably wasn't good enough and
perhaps I missed the attention of others in this group. This problem is
not as easy of an issue as it sounds. I think this is a serious security
hole in SQL Server and I hope Microsoft jumps into this thread. If you
disagree with me about how serious this is, please just be respectful that I
want a solution for my purposes nonetheless.
System: SQL 2005
Problem: I want to set up a user (from ASP.NET code) that has only execute
access on the database stored procs, but NOT access to system stored procs.
But, from my testing, even before I issue the issue any access or role
membership to a user, it seems that any users of a database can access
system stored procs (such as sp_stored_procedures, which exposes all the
user stored proc names that they can call with their execute access!).
Why is this a security problem, some ask? Because you never want to expose
your table names and proc names should a hacker somehow get your
username/pass. Also, even aside from knowing the company database
metadata, there are some system procs that do real damage, as mentioned in
this video:
http://www.learnsqlserver.com/Video...mdshell-sp.aspx
Some argue that blocking system stored procs can ruin Management Studio
features for that user. True, but that is fine for an ASP.NET user that
will never be in Management Studio.
So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
I've tried that and it doesn't work. You get an internal error about
permissions on server scoped catalog views or system stored procedures.
One not-so-good workaround? This issue has been discussed in only one other
forum I found. The solution was to create a same-named user in MASTER, and
then DENY execute on each individual system stored proc in master. That
sounds like a horribly tedious solution, and I'm not crazy about hand-typing
hundreds of system stored proc names. Besides, you would have to do this by
user. I tried doing it on a role, like a "db_executor" role I created, but
that doesn't work at all. See:
http://forums.microsoft.com/MSDN/Sh...407228&SiteID=1
I also found this Micrsoft article, but it didn't help because sys was not
recognized from inside my database, nor was system_objects:
http://msdn2.microsoft.com/en-us/library/ms178634.aspx
Pleeeeasssse help with a solution to blocking system stored procs, even if
you don't think it is that necessary for security. Thank you.On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
real.com> wrote:
> I have posted two other times and Kalen Delaney and David Browne were kind
> enough to respond. I thank them. Nonetheless, I am still without a
> solution and need help. My first subject probably wasn't good enough and
> perhaps I missed the attention of others in this group. This problem is
> not as easy of an issue as it sounds. I think this is a serious security
> hole in SQL Server and I hope Microsoft jumps into this thread. If you
> disagree with me about how serious this is, please just be respectful that
I
> want a solution for my purposes nonetheless.
> System: SQL 2005
> Problem: I want to set up a user (from ASP.NET code) that has only execut
e
> access on the database stored procs, but NOT access to system stored proc
s.
> But, from my testing, even before I issue the issue any access or role
> membership to a user, it seems that any users of a database can access
> system stored procs (such as sp_stored_procedures, which exposes all the
> user stored proc names that they can call with their execute access!).
> Why is this a security problem, some ask? Because you never want to expo
se
> your table names and proc names should a hacker somehow get your
> username/pass. Also, even aside from knowing the company database
> metadata, there are some system procs that do real damage, as mentioned in
> this video:http://www.learnsqlserver.com/Video.../Som
..
> Some argue that blocking system stored procs can ruin Management Studio
> features for that user. True, but that is fine for an ASP.NET user that
> will never be in Management Studio.
> So why not just do DENY EXECUTE on sp_stored_produres TO [user or role
]?
> I've tried that and it doesn't work. You get an internal error about
> permissions on server scoped catalog views or system stored procedures.
> One not-so-good workaround? This issue has been discussed in only one oth
er
> forum I found. The solution was to create a same-named user in MASTER, an
d
> then DENY execute on each individual system stored proc in master. That
> sounds like a horribly tedious solution, and I'm not crazy about hand-typi
ng
> hundreds of system stored proc names. Besides, you would have to do this
by
> user. I tried doing it on a role, like a "db_executor" role I created, bu
t
> that doesn't work at all. See:http://forums.microsoft.com/MSDN/Sh...407228&SiteID=1
> I also found this Micrsoft article, but it didn't help because sys was not
> recognized from inside my database, nor was system_objects:http://msdn2.microsof
t.com/...y/ms178634.aspx
> Pleeeeasssse help with a solution to blocking system stored procs, even if
> you don't think it is that necessary for security. Thank you.
This is easily done, I just confirmed that I can prevent a standard
SQL login from executing sp_help. Add the login to your user database
AND to the master database. In MASTER, go into the properties for
that login. Under "Securables", add "All objects belonging to the
schema" and choose the "sys" schema. Select the sprocs that you want
to prevent execution of, and check the "Deny" box.
I confirmed this by creating a login named "test", logged in with that
login, ran sp_help. I then denied access as described above, and now
get a permission denied error trying to run sp_help.|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173294431.368031.226630@.s48g2000cws.googlegroups.com...
> On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
> real.com> wrote:
> This is easily done, I just confirmed that I can prevent a standard
> SQL login from executing sp_help. Add the login to your user database
> AND to the master database. In MASTER, go into the properties for
> that login. Under "Securables", add "All objects belonging to the
> schema" and choose the "sys" schema. Select the sprocs that you want
> to prevent execution of, and check the "Deny" box.
> I confirmed this by creating a login named "test", logged in with that
> login, ran sp_help. I then denied access as described above, and now
> get a permission denied error trying to run sp_help.
>
Well, I agree that works, thanks, but that is the same concept as in the
http://forums.microsoft.com/MSDN/Sh...407228&SiteID=1
article which I posted earlier, except yours is through the GUI. The
problem, still, is that I would have to do this for hundreds of stored
procedures, which is a LOT of clicking considering you have to select a proc
in one pane and then choose the deny in the other pane. Doing that would
take a long time for just one user.
I appreciate the reply, but I cannot believe Microsoft has not given us an
easier way to block access to all system stored procs. I suppose they're
waiting for some big company to have this exploit used on them, and then
they'll issue a service pack a year later.|||On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
wrote:
> Well, I agree that works, thanks, but that is the same concept as in theht
tp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
> article which I posted earlier, except yours is through the GUI. The
> problem, still, is that I would have to do this for hundreds of stored
> procedures, which is a LOT of clicking considering you have to select a pr
oc
> in one pane and then choose the deny in the other pane. Doing that would
> take a long time for just one user.
> I appreciate the reply, but I cannot believe Microsoft has not given us an
> easier way to block access to all system stored procs. I suppose they're
> waiting for some big company to have this exploit used on them, and then
> they'll issue a service pack a year later.
Rather than nitpick about what Microsoft did or did not do, can't you
simply write a script that loops through sysobjects and issues the
necessary DENY's against every stored proc? This query will get you
all of the system procs if you run it in master:
SELECT *
FROM dbo.sysobjects
WHERE xtype = 'P'
I'm betting there's another way to do this using schemas, but I
haven't worked with them enough to know.|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173357526.661405.239300@.64g2000cwx.googlegroups.com...
> On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
> wrote:
> Rather than nitpick about what Microsoft did or did not do, can't you
> simply write a script that loops through sysobjects and issues the
> necessary DENY's against every stored proc? This query will get you
> all of the system procs if you run it in master:
> SELECT *
> FROM dbo.sysobjects
> WHERE xtype = 'P'
> I'm betting there's another way to do this using schemas, but I
> haven't worked with them enough to know.
>
I think Microsoft deserves the criticism. This is a security less-than-best
practice that is not easy to work around. Their default setup here is NOT
good. I will try the 1200 DENY statements and see if that messes up
anything. Thanks.

2nd REPOST (HELP!): Blocking access to system sprocs

I have posted two other times and Kalen Delaney and David Browne were kind
enough to respond. I thank them. Nonetheless, I am still without a
solution and need help. My first subject probably wasn't good enough and
perhaps I missed the attention of others in this group. This problem is
not as easy of an issue as it sounds. I think this is a serious security
hole in SQL Server and I hope Microsoft jumps into this thread. If you
disagree with me about how serious this is, please just be respectful that I
want a solution for my purposes nonetheless.
System: SQL 2005
Problem: I want to set up a user (from ASP.NET code) that has only execute
access on the database stored procs, but NOT access to system stored procs.
But, from my testing, even before I issue the issue any access or role
membership to a user, it seems that any users of a database can access
system stored procs (such as sp_stored_procedures, which exposes all the
user stored proc names that they can call with their execute access!).
Why is this a security problem, some ask? Because you never want to expose
your table names and proc names should a hacker somehow get your
username/pass. Also, even aside from knowing the company database
metadata, there are some system procs that do real damage, as mentioned in
this video:
http://www.learnsqlserver.com/VideoTutorials/Sql-Server-Video/352/Some-Dangerous-Stored-Procedures-xp-cmdshell-sp.aspx
Some argue that blocking system stored procs can ruin Management Studio
features for that user. True, but that is fine for an ASP.NET user that
will never be in Management Studio.
So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
I've tried that and it doesn't work. You get an internal error about
permissions on server scoped catalog views or system stored procedures.
One not-so-good workaround? This issue has been discussed in only one other
forum I found. The solution was to create a same-named user in MASTER, and
then DENY execute on each individual system stored proc in master. That
sounds like a horribly tedious solution, and I'm not crazy about hand-typing
hundreds of system stored proc names. Besides, you would have to do this by
user. I tried doing it on a role, like a "db_executor" role I created, but
that doesn't work at all. See:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
I also found this Micrsoft article, but it didn't help because sys was not
recognized from inside my database, nor was system_objects:
http://msdn2.microsoft.com/en-us/library/ms178634.aspx
Pleeeeasssse help with a solution to blocking system stored procs, even if
you don't think it is that necessary for security. Thank you.On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
real.com> wrote:
> I have posted two other times and Kalen Delaney and David Browne were kind
> enough to respond. I thank them. Nonetheless, I am still without a
> solution and need help. My first subject probably wasn't good enough and
> perhaps I missed the attention of others in this group. This problem is
> not as easy of an issue as it sounds. I think this is a serious security
> hole in SQL Server and I hope Microsoft jumps into this thread. If you
> disagree with me about how serious this is, please just be respectful that I
> want a solution for my purposes nonetheless.
> System: SQL 2005
> Problem: I want to set up a user (from ASP.NET code) that has only execute
> access on the database stored procs, but NOT access to system stored procs.
> But, from my testing, even before I issue the issue any access or role
> membership to a user, it seems that any users of a database can access
> system stored procs (such as sp_stored_procedures, which exposes all the
> user stored proc names that they can call with their execute access!).
> Why is this a security problem, some ask? Because you never want to expose
> your table names and proc names should a hacker somehow get your
> username/pass. Also, even aside from knowing the company database
> metadata, there are some system procs that do real damage, as mentioned in
> this video:http://www.learnsqlserver.com/VideoTutorials/Sql-Server-Video/352/Som...
> Some argue that blocking system stored procs can ruin Management Studio
> features for that user. True, but that is fine for an ASP.NET user that
> will never be in Management Studio.
> So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
> I've tried that and it doesn't work. You get an internal error about
> permissions on server scoped catalog views or system stored procedures.
> One not-so-good workaround? This issue has been discussed in only one other
> forum I found. The solution was to create a same-named user in MASTER, and
> then DENY execute on each individual system stored proc in master. That
> sounds like a horribly tedious solution, and I'm not crazy about hand-typing
> hundreds of system stored proc names. Besides, you would have to do this by
> user. I tried doing it on a role, like a "db_executor" role I created, but
> that doesn't work at all. See:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
> I also found this Micrsoft article, but it didn't help because sys was not
> recognized from inside my database, nor was system_objects:http://msdn2.microsoft.com/en-us/library/ms178634.aspx
> Pleeeeasssse help with a solution to blocking system stored procs, even if
> you don't think it is that necessary for security. Thank you.
This is easily done, I just confirmed that I can prevent a standard
SQL login from executing sp_help. Add the login to your user database
AND to the master database. In MASTER, go into the properties for
that login. Under "Securables", add "All objects belonging to the
schema" and choose the "sys" schema. Select the sprocs that you want
to prevent execution of, and check the "Deny" box.
I confirmed this by creating a login named "test", logged in with that
login, ran sp_help. I then denied access as described above, and now
get a permission denied error trying to run sp_help.|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173294431.368031.226630@.s48g2000cws.googlegroups.com...
> On Mar 7, 12:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-
> real.com> wrote:
>> I have posted two other times and Kalen Delaney and David Browne were
>> kind
>> enough to respond. I thank them. Nonetheless, I am still without a
>> solution and need help. My first subject probably wasn't good enough and
>> perhaps I missed the attention of others in this group. This problem
>> is
>> not as easy of an issue as it sounds. I think this is a serious security
>> hole in SQL Server and I hope Microsoft jumps into this thread. If you
>> disagree with me about how serious this is, please just be respectful
>> that I
>> want a solution for my purposes nonetheless.
>> System: SQL 2005
>> Problem: I want to set up a user (from ASP.NET code) that has only
>> execute
>> access on the database stored procs, but NOT access to system stored
>> procs.
>> But, from my testing, even before I issue the issue any access or role
>> membership to a user, it seems that any users of a database can access
>> system stored procs (such as sp_stored_procedures, which exposes all the
>> user stored proc names that they can call with their execute access!).
>> Why is this a security problem, some ask? Because you never want to
>> expose
>> your table names and proc names should a hacker somehow get your
>> username/pass. Also, even aside from knowing the company database
>> metadata, there are some system procs that do real damage, as mentioned
>> in
>> this
>> video:http://www.learnsqlserver.com/VideoTutorials/Sql-Server-Video/352/Som...
>> Some argue that blocking system stored procs can ruin Management Studio
>> features for that user. True, but that is fine for an ASP.NET user that
>> will never be in Management Studio.
>> So why not just do DENY EXECUTE on sp_stored_produres TO [user or role]?
>> I've tried that and it doesn't work. You get an internal error about
>> permissions on server scoped catalog views or system stored procedures.
>> One not-so-good workaround? This issue has been discussed in only one
>> other
>> forum I found. The solution was to create a same-named user in MASTER,
>> and
>> then DENY execute on each individual system stored proc in master. That
>> sounds like a horribly tedious solution, and I'm not crazy about
>> hand-typing
>> hundreds of system stored proc names. Besides, you would have to do this
>> by
>> user. I tried doing it on a role, like a "db_executor" role I created,
>> but
>> that doesn't work at all.
>> See:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
>> I also found this Micrsoft article, but it didn't help because sys was
>> not
>> recognized from inside my database, nor was
>> system_objects:http://msdn2.microsoft.com/en-us/library/ms178634.aspx
>> Pleeeeasssse help with a solution to blocking system stored procs, even
>> if
>> you don't think it is that necessary for security. Thank you.
> This is easily done, I just confirmed that I can prevent a standard
> SQL login from executing sp_help. Add the login to your user database
> AND to the master database. In MASTER, go into the properties for
> that login. Under "Securables", add "All objects belonging to the
> schema" and choose the "sys" schema. Select the sprocs that you want
> to prevent execution of, and check the "Deny" box.
> I confirmed this by creating a login named "test", logged in with that
> login, ran sp_help. I then denied access as described above, and now
> get a permission denied error trying to run sp_help.
>
Well, I agree that works, thanks, but that is the same concept as in the
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
article which I posted earlier, except yours is through the GUI. The
problem, still, is that I would have to do this for hundreds of stored
procedures, which is a LOT of clicking considering you have to select a proc
in one pane and then choose the deny in the other pane. Doing that would
take a long time for just one user.
I appreciate the reply, but I cannot believe Microsoft has not given us an
easier way to block access to all system stored procs. I suppose they're
waiting for some big company to have this exploit used on them, and then
they'll issue a service pack a year later.|||On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
wrote:
> Well, I agree that works, thanks, but that is the same concept as in thehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
> article which I posted earlier, except yours is through the GUI. The
> problem, still, is that I would have to do this for hundreds of stored
> procedures, which is a LOT of clicking considering you have to select a proc
> in one pane and then choose the deny in the other pane. Doing that would
> take a long time for just one user.
> I appreciate the reply, but I cannot believe Microsoft has not given us an
> easier way to block access to all system stored procs. I suppose they're
> waiting for some big company to have this exploit used on them, and then
> they'll issue a service pack a year later.
Rather than nitpick about what Microsoft did or did not do, can't you
simply write a script that loops through sysobjects and issues the
necessary DENY's against every stored proc? This query will get you
all of the system procs if you run it in master:
SELECT *
FROM dbo.sysobjects
WHERE xtype = 'P'
I'm betting there's another way to do this using schemas, but I
haven't worked with them enough to know.|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173357526.661405.239300@.64g2000cwx.googlegroups.com...
> On Mar 7, 9:40 pm, "K. Abit" <sendtothegroupo...@.this-is-not-real.com>
> wrote:
>> Well, I agree that works, thanks, but that is the same concept as in
>> thehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407228&SiteID=1
>> article which I posted earlier, except yours is through the GUI. The
>> problem, still, is that I would have to do this for hundreds of stored
>> procedures, which is a LOT of clicking considering you have to select a
>> proc
>> in one pane and then choose the deny in the other pane. Doing that
>> would
>> take a long time for just one user.
>> I appreciate the reply, but I cannot believe Microsoft has not given us
>> an
>> easier way to block access to all system stored procs. I suppose they're
>> waiting for some big company to have this exploit used on them, and then
>> they'll issue a service pack a year later.
> Rather than nitpick about what Microsoft did or did not do, can't you
> simply write a script that loops through sysobjects and issues the
> necessary DENY's against every stored proc? This query will get you
> all of the system procs if you run it in master:
> SELECT *
> FROM dbo.sysobjects
> WHERE xtype = 'P'
> I'm betting there's another way to do this using schemas, but I
> haven't worked with them enough to know.
>
I think Microsoft deserves the criticism. This is a security less-than-best
practice that is not easy to work around. Their default setup here is NOT
good. I will try the 1200 DENY statements and see if that messes up
anything. Thanks.

2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>