Basic permissions change, sp_tables issue

[MVisconte]

Or, "Too many hands"...

We had another group "run some scripts" just before we started having various users experience access issues. The acting SA has reviewed permissions and "patched" as best he can, but I have question relating to one of the symptoms we have noticed:

We are having event log entries being raised regarding permissions and "sp_tables". I have tried eye-balling the permissions, also, to see what differences I can see between the several servers (specifically, those that DON'T have the problem). In addition to the event log entries, we have a peculiarity in setting up ODBCs. We would normally see a list of available databases in the "Change Default Database" option, when setting up an ODBC connection using our standard "datareader" account. Now, the list is "empty", although we can MANUALLY type in an existing database and get a good connection. If we use the SAs account, the list is fully and correctly populated.

Anybody know what perms I can look for to bring things back under their normal smooth sailing orders?

The master shows public and dbo as having permissions to sp_tables. Our Remedy database datareader account is a member of datareader and public, but can't see table lists.

Aw shucks. I just tried setting up an ODBC again, and NOW I get Select permission denied on object "syscharsets", database "master", owner "dbo".

In the middle east, they cut people's hands off for messing with stuff they ought not. Sigh. But then, I'm sure that when I was starting out, I would have lost my hands a couple times over.

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[mrdenny]

Sounds to me like the scripts that were run removed publics permissions to some tables and procedures.

Run this on a server with no problems, then grant the access listed on the non-working server.

select * 
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where grantee = 'public'

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MVisconte]

Thank you, Denny. I agree with your tagline, and I appreciate your help.

I will run that shortly and report what the results in the morning.

Marc

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[mrdenny]

Sounds good. Good luck.

And thanks.



Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MVisconte]

Logged in as SA, the results look to be the same for the most part:

("Good" system)
Table name, priv type, grantable
syssegments, select, no
sysconstraints, select, no

The local (munged) system adds
dtproperties, insert, no
dtproperties, delete, no
dtproperties, references, no
dtproperties, update, no
dtproperties, select, no

Sigh.

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports