April 12, 2003 at 7:28 am
Well, not really gone; all of a sudden I just can't see them anymore in EM.
This is weird. I was working on one particular client machine (XP Pro, SQL Server 7.0) and all was well. I don't know what I did (it must have been something), but all of a sudden I started getting this "There are no items in this view" message in EM when I attempted to list the stored procedures in one particular database on one particular server when logged onto the domain in one particular account. If I attempt to view the list of stored procedures in other databases on the server, all is well. If I log onto the domain on the client machine using a different account, the stored procedures magically reappear in the particular database. Log in using the offending account, and they are gone again. They are not really gone; the display heading shows the correct number of stored procedures defined in the database, I just can't see any of them - there is an empty listing. Views and tables, etc in the offending database are fine, just the stored procedures won't display. I can execute the stored procedures from the offending account from either VB or QA; I just can't see them in EM.
Anybody have any idea what happened, and more importantly, how to restore the display of the stored procedures for this particular database for this particular account?
April 12, 2003 at 10:44 am
Where the stored procedures created using dbo user? That's the only thing I can think of is that they were created under a user name that doesn't have access to see the stored procedures through enterprise manager. I'm guessing you can see them in syscomments?
Darren
Darren
April 12, 2003 at 11:15 am
The procedures were created under the offending account. The account is the owner of the database. One minute they were there, I did something (I don't know what) and minutes later I notived that they were 'gone' in EM, at least as far as that one account is concerned. And that one account can connect to the database via VB and execute the procedures that it can't 'see' in EM - so I think this must be some sort of EM configuation issue. I have tried unregistering the server then reregistering it, but the SPs are still not visible. Bizarre.
April 12, 2003 at 11:25 am
That's strange. I don't have any other ideas. It just seems security related but not if it was created under db owner account. Did you try viewing them in the syscomments table?
select * from syscomments where name like '%stored proc name%'
Darren
Darren
April 13, 2003 at 6:19 pm
Also related to this, How do we see system stored procedures and system tables in SQL Sever 6.5 EM? I can grant access using DBArtisan for them. But I don't see them. Is it normal. Is there any way we can make them appear?!
Regards,
Murali Damera.
.
April 14, 2003 at 8:37 am
jemmer,
Logged into query analyzer, execute this:
select so.name 'ProcName', su.name 'UserName'
from sysobjects so
inner join sysusers su on su.uid = so.uid
where so.type = 'P'
It would show you if the procedures still exist and who the owner is. If the procedure shows up here, there must be a permissions issue.
guarddata-
April 14, 2003 at 6:06 pm
I appreciate everyone's help. I managed to figure out what happened - sort of - at least the problem has been corrected, even though I still don't know why it happened.
The syscomments table showed the procedures were intact, and other accounts successfully running EM showed the protection on the procedures was correct.
I was further puzzled today by this since I uninstalled then reinstalled SQL Server (and the client tools) on the offending machine, and to my surprise, the problem still existed - one particular account could not 'see' the stored procedures in EM in one database on one server. The account could see other SPs in other database or on other servers. Then I found that the same account logged onto another machine could see the procedures just fine. This gave me a clue, since the problem survived a reinstall, and only exhibited itself on this one machine. I figured there must have been a registry setting for the account but a search of the registry revealed nothing.
I then thought it maybe was a setting in the Documents and Settings folder. I poked around there and discovered a file in 'C:\Documents and Settings\<loginaccount.domain>\Application Data\Microsoft\MMC' named something like 'MS SQL Server Enterprise Manager'. I may not have the file name quite right, but the name was something like that and I thought it quite suspicious. I noticed that no other accounts had this file in the similar directory for them. I moved the file out of the directory then restarted EM, and lo and behold, the SPs reappeared! I moved it back, and they disappeared again.
EM is an MMC snap-in. Apparently in XP, that file got somehow created, and I would guess that the file was corrupt in some way. Its presence caused the problem, and deleting it fixed the problem.
It is strange that I cannot find anything like that file on my Win2000 machine.
The problem has been fixed, but it's one of those where you tiptoe quietly away, happy that the problem no longer exists, but disquieted by the fact that you have no idea why it happened in the first place...
Strange...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply