SQL Server public permissions required?

Honored Contributor.

SQL Server public permissions required?

We have some of our Trim datasets installed as SQL Server 2000 databases. One thing SQL Server 2000 and back was bad at was giving the Public role way too many permissions.On the SQL Server in question I have revoked public permissions from most databases except for the Master and MSDB databases. This is for security reasons as some of our customers require our databases to be run with certain settings from a security perspective. 


I have gotten alerts from the Trim server occasionally and have been told by the user that what they were doing failed. What they were doing was the following (she was running Trim client 6.2.5build 1300 and the server is 6.2.4 build 1240):



I performing a

Search -> Find Records -> Advanced -> Search by "Title Word" -> (give the title word I want to search on) -> OK Then, I got this errors



The error I got from the Event Processor was:



TRIM Workgroup Server running on trimserver.ourcompany.local reported the following message:
 Error : 52  User: TRIM -  The SQL transaction 'dropWorkTable' failed for user USERSPC:3320 on command IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb.dbo.##TStmpd929f4dbef3e40b189d6b211bd95835e')) DROP TABLE ##TStmpd929f4dbef3e40b189d6b211bd95835e.
 Details : SELECT permission denied on object 'sysobjects', database 'tempdb', owner 'dbo'.
 Detailed ADO Errors
 Number = 0x80040e09
 Source = Microsoft OLE DB Provider for SQL Server
 Description = SELECT permission denied on object 'sysobjects', database 'tempdb', owner 'dbo'.
 SQLState = 42000
 NativeError = 229
This E-mail Message has been automatically generated by TRIM Context (trimserver.ourcompany.local).

It's clear to me that Trim is relying on having the ability to select from the tempdb via the Public role that is expects to have. Should I grant Trim privileges on all the temp tables if I don't want to grant them to public? Is it documented somewhere the necessary privileges in other databases that Trim needs? I think I have the Trim user set up correctly but in this case the Trim user is trying to select from a database that isn't it's own.


Jason Boswell
Outstanding Contributor.

Re: SQL Server public permissions required?

I understand that when complex searches are run by a TRIM user the TRIM service account running the workgroup server will execute complex SQL transactions. Some of these include creating temporary tables. The happens in particular when a large resultset is returned from a document content indexing search. General users should not have any access to the databases but the account running the workgroup server service will need dbo access to the main TRIM database and where temporary tables are created.

Honored Contributor.

Re: SQL Server public permissions required?

It does appear from the SQL I showed that tables are created. What's strange is that the SQL shows the SELECT is failing and not the temp table creation. I wonder if the temp table is even created because I don't see how they could be.