Listing permissions on Stored Procedures via query.

How do I list the permissions on a stored procedure with a query?
I'm trying to remove Public from some xp_* procedures but I'd like to see
what Public is assigned to first.
Thanks much.Look up the system procedure sp_helprotect in SQL Server Books Online.
Anith|||"Horst" <Horst@.discussions.microsoft.com> wrote in message
news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> How do I list the permissions on a stored procedure with a query?
> I'm trying to remove Public from some xp_* procedures but I'd like to see
> what Public is assigned to first.
>
> Thanks much.
You could reverse engineer the sp_helprotect sproc, but this should work
for you as well.
--
USE master
GO
CREATE TABLE #Foo (
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType varchar(50),
Action varchar(50),
[Column] sysname NULL
)
INSERT #Foo
EXEC sp_helprotect
SELECT *
FROM #Foo
WHERE OBJECT LIKE 'xp%'
AND Grantee = 'public'
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the help.
"Rick Sawtell" wrote:

> "Horst" <Horst@.discussions.microsoft.com> wrote in message
> news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> You could reverse engineer the sp_helprotect sproc, but this should work
> for you as well.
> --
> USE master
> GO
>
> CREATE TABLE #Foo (
> Owner sysname,
> Object sysname,
> Grantee sysname,
> Grantor sysname,
> ProtectType varchar(50),
> Action varchar(50),
> [Column] sysname NULL
> )
> INSERT #Foo
> EXEC sp_helprotect
> SELECT *
> FROM #Foo
> WHERE OBJECT LIKE 'xp%'
> AND Grantee = 'public'
> DROP TABLE #Foo
> --
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Thanks for the help.
"Anith Sen" wrote:

> Look up the system procedure sp_helprotect in SQL Server Books Online.
> --
> Anith
>
>

listening on... interfaces

The errorlog on my installation reports the following:
SQL server listening on MY.INTERNAL.IP.1: 22847.
SQL server listening on MY.PUBLIC.IP: 22847.
SQL server listening on MY.INTERNAL.IP.2: 22847.
SQL server listening on 127.0.0.1: 22847.
SQL server listening on TCP, Named Pipes.
SQL Server is ready for client connections
How can I configure SQL to not listen at all on the public IP?
Thanks.
-RickHi
You have to configure IPSec at NIC level to disallow connections on the
public IP. By default and design, SQL Server will always listen on all IP's
and SQL Server can not be configured not to listen on them.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:67BA2827-A522-40DB-B288-0C63A9A1797B@.microsoft.com...
> The errorlog on my installation reports the following:
> SQL server listening on MY.INTERNAL.IP.1: 22847.
> SQL server listening on MY.PUBLIC.IP: 22847.
> SQL server listening on MY.INTERNAL.IP.2: 22847.
> SQL server listening on 127.0.0.1: 22847.
> SQL server listening on TCP, Named Pipes.
> SQL Server is ready for client connections
> How can I configure SQL to not listen at all on the public IP?
> Thanks.
> -Rick
>|||Wow, I have to admit I don't know the first thing about IPSec. Is this a
configuration I can do in the OS?
Am I barking up the right tree? Isn't it common to not want your SQL Server
to be listening to the wild wild internet?
Thanks,
-Rick
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> You have to configure IPSec at NIC level to disallow connections on the
> public IP. By default and design, SQL Server will always listen on all IP'
s
> and SQL Server can not be configured not to listen on them.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:67BA2827-A522-40DB-B288-0C63A9A1797B@.microsoft.com...
>
>|||> Isn't it common to not want your SQL Server
> to be listening to the wild wild internet?
Definitely. That's what firewalls are for.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:404BBC8C-884B-4ED5-8C24-703E44D0F35C@.microsoft.com...[vbcol=seagreen]
> Wow, I have to admit I don't know the first thing about IPSec. Is this a
> configuration I can do in the OS?
> Am I barking up the right tree? Isn't it common to not want your SQL
> Server
> to be listening to the wild wild internet?
> Thanks,
> -Rick
> "Mike Epprecht (SQL MVP)" wrote:
>|||"Dan Guzman" wrote:
> Definitely. That's what firewalls are for.
Thanks Dan. The system is a typical (dual-homed) SBS2003. I thought that I
got a level of firewalling with SBS. I guess I was naive. So I'm not sure
of which way to go. Would you folks recommend:
1) New hardware firewall (any suggestions?)
2) TCP/IP (and UDP) filtering in Advanced TCP/IP Settings?
3) IPSec policies?
4) Move my SQL databases to an internal server?
5) Combination of above?|||> 1) New hardware firewall (any suggestions?)
> 2) TCP/IP (and UDP) filtering in Advanced TCP/IP Settings?
> 3) IPSec policies?
> 4) Move my SQL databases to an internal server?
> 5) Combination of above?
Every company I've worked for uses a hardware firewall and deploys SQL
Servers on an internal network. You can get the job done using
IPSec/Filtering on your SQL Servers but it's best not to let the traffic in
at all. I'm not a network guy so you probably don't want my recommendations
on hardware :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:60607EA5-883E-4882-9EC7-2F0B4CB5FEBC@.microsoft.com...
> "Dan Guzman" wrote:
> Thanks Dan. The system is a typical (dual-homed) SBS2003. I thought that
> I
> got a level of firewalling with SBS. I guess I was naive. So I'm not
> sure
> of which way to go. Would you folks recommend:
> 1) New hardware firewall (any suggestions?)
> 2) TCP/IP (and UDP) filtering in Advanced TCP/IP Settings?
> 3) IPSec policies?
> 4) Move my SQL databases to an internal server?
> 5) Combination of above?
>