.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Can execute proc but can't see it in SSMS object explorer

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

I have setup a user account that has limited rights to execute certain stored procedures.  I can execute these procedures but when I login into SSMS with this user I can't see these sprocs in the object explorer list.  Is there a special permission this user needs to be able to enumerate available sprocs?

I'm running SQL Server 2008 R2 x64.

View Complete Post

More Related Resource Links

SSMS/sqlwb.exe hangs when expanding indexes on table in object explorer when table has 10 or more in

Greetings, I have had an anoying problem in SSMS for some time, it seems to hang in the object explorer when I try to expand indexes on a table that has 10 or more indexes. It works fine when we have fewer indexes but when there are 10 or 12, it opens up and shows the indexes and you can work with them (script them, drop, etc.) but the "stop" button in object explorer shows red and is unresponsive as in you can't click and stop it. Eventually I will shut down SSMS and reopen to clear it, but in task manager there is still the sqlwb.exe running and its using 50% or more of the CPU and I have to end that process. I am running SQL 2005 management studio 9.00.4035.00. Any ideas on a fix out there would be appreciated.

?? SSMS "Object Explorer Details" Can't Find Constraints ??


Hi everyone,

I'm using SQL Server Management Studio 2008 R2.

As you know, using the "Object Explorer Details" window (pressing F7) you can see details related to the item selected in "Object Explorer". For example, (assuming you have Northwind installed), select the Northwind database in "Object Explorer" and press F7.

Now, in the "Search" bar at the top of the"Object Explorer Window", let's search for the "DF_Orders_Frieght" constraint defined in the "Orders" table. Just type, "DF_Orders_Freight" in the Search box and press Enter. For some reason, SSMS cannot find the constraint, or any constraint, for that matter. There seems to be a similar problem is searching for column names.

The Search box is certainly a nice feature but why doesn't it find column names or constraint names? It's especially annoying when a constraint fails and we need to find out where that constraint is defined.

Thanks much

Exception when login with sa in Object Explorer within MSSM Studio

Has anyone got an idea what might have caused the following. I have been able to login using SQL Server Authentication using the sa account with no problems within the object explorer. Not sure why I get an error (see below) when I try and login today. I can use the sa account via VS2010 IDE to connect to SQL Server, I can use sa account going via Database Engine Query within MSSM Studio, which uses the same SQL Server Authentication dialog. But when trying to connect via Object Explorer I get the following error. TITLE: Connect to Server ------------------------------ Cannot connect to --------- ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ A severe error occurred on the current command.  The results, if any, should be discarded. A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ The Event Log gives an error of: A user request from the session with SPID 51 generated a fatal exception. SQL Server is terminating this session. Contact Produc

SSMS 2008 Activity Monitor Activity Monitor is unable to execute queries against server (SQL Server

I get the following error when running SQL Server SSMS 2008 (32-bit) on my (Windows 7 64-bit) workstation against our SQL Server 2005 (32-bit) development server running Windows Server 2003 (32-bit): TITLE: Microsoft SQL Server Management Studio ------------------------------ The Activity Monitor is unable to execute queries against server <my server\instance name>. Activity Monitor for this instance will be placed into a paused state. Use the context menu in the overview pane to resume the Activity Monitor. ------------------------------ ADDITIONAL INFORMATION: Unable to find SQL Server process ID 2256 on server VTX-DEV-01 (Microsoft.SqlServer.Management.ResourceMonitoring)   I can expand the panes and all display data except Overview and Data File I/O. This is true for all instances on that server. The SSMS 2005 Activity Monitor (also on my workstation) works fine when connected to that server. I checked for the registry setting to disable the performance counters on both my machine and the server, and it was not there. I started the Performance Counter DLL Host on my machine, but that made no difference. I can see all Processor counters on the server from my local Performance Monitor. I ran "lodctr /R" on both systems, but that didn't help. The only SQL counters I see on the server (from my workstation) are SQLServer:SSIS Pipeline and SQLServer:SSIS

The EXECUTE permission was denied on the object 'proc_UpdateVirusInfo'

Hi All,   SharePoint 2007 x64 w/SP1, SQL 2005 x64, ForeFront for SharePoint w/SP1, running on Windows Server 2003 x64 R2 w/SP2.   We originall setup this server using only a couple service accounts. That was a bad idea so a few weeks ago I went over everything and setup or service accounts according to Microsofts recommendations. (http://technet.microsoft.com/en-us/library/cc263445.aspx)   I've gone over the requirements a dozen times and everything is set perfect.   I have two issues.   1. Randomly we have a SQL permission issue popping up, this abouts a couple times a day (some days none), the event log will fill about with anywhere from 15 to 800 of the same event.   Event Type: ErrorEvent Source: Windows SharePoint Services 3Event Category: Database Event ID: 5214Date:  3/11/2008Time:  1:28:30 PMUser:  N/AComputer: TRUMANDescription:Insufficient SQL database permissions for user 'PortalPool' in database 'SharePoint_AdminContent_45f0f48e-c1b1-463c-99e3-ea6648beaf37' on SQL Server instance 'Truman'. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object 'proc_UpdateVirusInfo', database 'SharePoint_AdminContent_45f0f48e-c1b1-463c-99e3-ea6648beaf37', schema 'dbo'. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.   PortolPool is our service account for the IIS Poo

Why is 2005 EM -- Object explorer, programmability, tables locksup?

Why is 2005 EM -- Object explorer, programmability, tables locks up while rebuild index job on that database running?

SQL Server 2005 permissions error. The EXECUTE permission was denied on the object "xp_instance_reg


I have a SQL Server host running SQL 2005 9.00.4294 x86 Standard Edition running on Windows build 2195 SP4.  My client workstation is running only SQL Server 2005 workstaion components.   When a user of the host who has db_owner access attempts to view the properties of a table by right-clicking the table and then clicking Properties, the following error mesage is displayed:  

"The EXECUTE permission was denied on the object "xp_instance_regread", database 'mssqlsystemresource', schema 'sys'

For security purposes, I do not want to grant execute on xp_instance_regread to these users. Does anyone know of a workaround that will allow members of db_owner to access table properties using the abovementioned method and does not require execute access to be granted?




Object Explorer/Server Explorer Error

I have just installed the released VS2005 as well as the released SQL2005.  When ever I try to browse my SQL Server with either the Object Explorer in MS SQL Server Management Studio or the Server Explorer in VS2005 I get the following error:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.OLE.Interop.IServiceProvider'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{6D5140C1-7436-11CE-8034-00AA006009FA}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Does any one have possible solutions for me? Any help would be greatly appreciated.


EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource'


HI There

I have a user login that is executing an sp. It gets the follwoing error:

Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1

EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1

EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1

EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

How to Execute Nested .sql from File System in SSMS?


Hi All,

I have several .sql files stored in my file system and I am trying to find a way to execute them from within SSMS without loading then first.  The idea is something like having a file "C:\SQL\MasterFile.sql" that contains something like

run/execute "C:\SQL\File1.sql"

run/execute "C:\SQL\File1.sql"

I will load MasterFile into a SSMS query window and run it and have it execute the other files from the file system.


How to execute a oracle stored proc?


plz help me...



when i try to execute a oracle sp i got the bellow error msg.am usin oracle10g

ORA-06550: line 2, column 19:
PLS-00201: identifier 'CURSOR' must be declared
ORA-06550: line 2, column 15:
PL/SQL: Item ignored
ORA-06550: line 5, column 45:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored



EXECUTE permission was denied on the object 'proc_putObject',



I am getting the following error, while I try to take the backup of sharepoint application.

EXECUTE permission was denied on the object 'proc_putObject',


Srikanth Reddy

Hide inaccessible databases from object Explorer


Hi All

Does anybody know if it is possible to hide databases in the object explorer which the user doesn' t have access too

I currently connect to a shared hosting solution and can see all of the databases on the server approx 80 + and it becomes a bit of chore to find my 3 databases in this growing list .



create proc with execute as


I read the documentation http://msdn.microsoft.com/en-us/library/ms188354(SQL.90).aspx but can't seem to replicate what I seemingly understood from this.

From what I understand if you just create a stored proc without the 'execute as' option, then the default is 'execute as caller'. Am I correct about this?

To test this scenario, I did the following.

(1) As a sysadmin, I created a simple stored proc in a test database to simply delete some data from a table in that database. The stored proc was created without the execute as option. (so if my understanding is correct, then it executes under the permission of the caller.)

(2) As a sysadmin, I created a dummy login and mapped that login to the test database and gave the user no other permission than to execute that stored proc. So, if that user tries to delete anything from that table, he gets a permission denied error.

(3) Next, I execute the test stored procedure logged in as the dummy user. The procedure runs (since the dummy user was given the execute permission), but to my surprise the procedure deletes the data.

What am I missing?

As a side note, after I did the above test, I alter the stored proc and played with both 'execute as caller' and 'execute as 'dummy'' and stil

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.


I am getting the following error when I execute an SSRS report.

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

The report is executing a stored proc. Which in turn is running an Agent Job.

When I run this report through BIDS it works fine. It is only when I run it though Report Manager I get a problem.

Can anybody help?

Mr Shaw

is can be a different result from execute procedure from job and from ssms?

i have a different result (row count of select stetment) when i execute this from job and when from ssmss

EXECUTE permission on a stored proc


Does EXECUTE permission on a stored proc automatically give permissions to the caller (if EXECUTE AS CALLER is set, by default) to any and all objects in the proc, without having to give them directly to the caller?

For example, if I had a table with data in it, called "Table_A" and I wrote a stored procedure that had "DELETE FROM TABLE_A" and left the default "execute as caller".  If I give Execute permission to Bob and he executes this proc, it will delete all data from TABLE_A, even though Bob hasn't been given DELETE permissions on that table?

I might be missing something here, but if Bob tried to just run "DELETE FROM TABLE_A" in SSMS and it balked at him, he could write a stored proc with this statement, and execute the proc and delete the data?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend