MSSQL 2005 and system stored procedures

[hafa]

When using Dreamweaver with MSSQL 2000, the only stored procedures that would display when defining a recordset would be those stored procedures that were created by the user whose login was in the connection string for the recordset being created.

In MSSQL 2005, however, all system stored procedures (I'd estimate that there are over 600 of them) attempt to display, locking up DW and making it essentially useless.

Two questions come to mind:

1. Is it secure that ANY login with default permissions applied should be able to see all of the system procedures, many of which deal with sensitive areas of the DB?
   
2. Is there any way to have a given login NOT show system stored procedures which were not created by said login?

Thanks for any feedback...

 

[Stephen]

you can see them, but have no access to do anything to them

2. not that i have found.

 

[hafa]

Thanks, Stephen

The work around I employed is to simply make a login name that starts with any letter above "S", then kill the loading process after the procedures you've created with said login name display.

Geez, what way MS thinking, anyway?

 

[daddywhite]

FIXED! - MSSQL 2005 and system stored procedures

In order to fix this issue, simply do the following:

Open up:

C:\Program Files\Adobe\Adobe Dreamweaver CS3\configuration\Connections\Scripts\ASP_Vbs\_mmDBScripts\MMHTTPDB.js

and in the function ConnGetProcedures(SchemaName,CatalogName)

change:

var JSVariant = CreateJSArray(CatalogName,SchemaName,"","");

TO

var JSVariant = CreateJSArray(CatalogName,"dbo","","");

All fixed for SQL2005 and doesnt affect SQL2000.

 

[daddywhite]

Better solution!!!! :

WHen editing your Dreamweaver Connection, click "Advanced", and enter "dbo" into the "Schema" box.

 

[KrisSiegel]

If you take advantage of schemas then enter the schema where your stored procs are located.

 

[hafa]

Holy thread resurection!

On a different/more contemporary note:

When using stored procedures on MSSQL2005, the only stored procedures that have permissions to execute are those created within the schema of the dbo.

When attempting to add permissions to a given procedure in EM, all seems to go well, but the permissions don't persist. (When calling up properties>permissions after applying them, the permissions are no longer there. The same happens when trying to edit schema permissions.)

Is there any way to create/run a stored procedure with a schema OTHER than the DBO?

 

[KrisSiegel]

When using stored procedures on MSSQL2005, the only stored procedures that have permissions to execute are those created within the schema of the dbo.

This should work. How are you attempting to grant permissions to the stored procedures?

I typically create a deployment script that creates the stored procs I need and include the GRANT command

GRANT EXECUTE ON [MySchema].[MyStoredProc] TO MyUser

However, that method will not work on system stored procedures. Are you really using system stored procedures as indicated in your title? If so, I would suggest avoiding them as they're really never needed except for maintenance.

 

[hafa]

This should work. How are you attempting to grant permissions to the stored procedures?

The same method as used successfully for table permissions:

In MSSQL Server Management Studio:

Right click on stored procedure, go to properties, Click on permissions, click "add", add the user, set the permissions.

However, that method will not work on system stored procedures....

These are not system stored procedures, as I said:

On a different/more contemporary note: