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...
>