Permission of SP_FULLTEXT_TABLE in stored procedure

Permission of SP_FULLTEXT_TABLE in stored procedure

Post by Guogan » Sat, 08 Nov 2003 05:03:52



Hi,
I want to run this within my sotred procedure:

Such that an authorized user can connect to database, and run this stored
procedure to initiate a rebuild of index.

The problem is I get an exception in my C# code which calls this stored
procedure:
System.Data.SqlClient.SqlException: User does not have permission to perform
this action.

I can execute the following stored procedure in the same way without any
problem:
EXEC SP_XML_PREPAREDOCUMENT

So, what is the difference? Why am I getting an exception?

Thanks,
Guogang

 
 
 

Permission of SP_FULLTEXT_TABLE in stored procedure

Post by John Kan » Sat, 08 Nov 2003 05:57:27


Guogang,
The most likely difference is one of permission, as you need sysadmin
permission level in order to execute sp_fulltext_table.
See SQL Server 2000 BOL title "sp_fulltext_table" - "Permissions
Only members of the sysadmin fixed server role, db_owner and db_ddladmin
fixed database roles, and the object owner can execute sp_fulltext_table. "

FYI, you only need to post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext.
Regards,
John


> Hi,
> I want to run this within my sotred procedure:

> Such that an authorized user can connect to database, and run this stored
> procedure to initiate a rebuild of index.

> The problem is I get an exception in my C# code which calls this stored
> procedure:
> System.Data.SqlClient.SqlException: User does not have permission to
perform
> this action.

> I can execute the following stored procedure in the same way without any
> problem:
> EXEC SP_XML_PREPAREDOCUMENT

> So, what is the difference? Why am I getting an exception?

> Thanks,
> Guogang


 
 
 

Permission of SP_FULLTEXT_TABLE in stored procedure

Post by Guogan » Sat, 08 Nov 2003 21:18:12


Thanks for the information.

But, I really want to let authors have the ability to rebuild the index, so
that their newly added content can be searchable immediately. But, due to
security reason, they won't be logged in as owner of the table.

Any suggestion how I can achieve the goal?

Thanks again,
Guogang


> Guogang,
> The most likely difference is one of permission, as you need sysadmin
> permission level in order to execute sp_fulltext_table.
> See SQL Server 2000 BOL title "sp_fulltext_table" - "Permissions
> Only members of the sysadmin fixed server role, db_owner and db_ddladmin
> fixed database roles, and the object owner can execute sp_fulltext_table.
"

> FYI, you only need to post FTS related questions to the newsgroup:
> microsoft.public.sqlserver.fulltext.
> Regards,
> John



> > Hi,
> > I want to run this within my sotred procedure:



- Show quoted text -

Quote:

> > Such that an authorized user can connect to database, and run this
stored
> > procedure to initiate a rebuild of index.

> > The problem is I get an exception in my C# code which calls this stored
> > procedure:
> > System.Data.SqlClient.SqlException: User does not have permission to
> perform
> > this action.

> > I can execute the following stored procedure in the same way without any
> > problem:
> > EXEC SP_XML_PREPAREDOCUMENT

> > So, what is the difference? Why am I getting an exception?

> > Thanks,
> > Guogang

 
 
 

Permission of SP_FULLTEXT_TABLE in stored procedure

Post by John Kan » Sat, 08 Nov 2003 22:12:06


You're welcome, Guogang,
It's not table owner that's required, but the sysadmin, db_owner or
db_ddladmin permission that is required. That will only be possible if you
give the authors these rights, but I suspect that you will not want to do
that as well, so "catch-22"....

An alternative, *might* be that you setup a schedule job task that does this
with the correct access rights, dbname & tablename and you provide some
means for the authors to request the running of this job.

Regards,
John


> Thanks for the information.

> But, I really want to let authors have the ability to rebuild the index,
so
> that their newly added content can be searchable immediately. But, due to
> security reason, they won't be logged in as owner of the table.

> Any suggestion how I can achieve the goal?

> Thanks again,
> Guogang



> > Guogang,
> > The most likely difference is one of permission, as you need sysadmin
> > permission level in order to execute sp_fulltext_table.
> > See SQL Server 2000 BOL title "sp_fulltext_table" - "Permissions
> > Only members of the sysadmin fixed server role, db_owner and db_ddladmin
> > fixed database roles, and the object owner can execute
sp_fulltext_table.
> "

> > FYI, you only need to post FTS related questions to the newsgroup:
> > microsoft.public.sqlserver.fulltext.
> > Regards,
> > John



> > > Hi,
> > > I want to run this within my sotred procedure:


> > > Such that an authorized user can connect to database, and run this
> stored
> > > procedure to initiate a rebuild of index.

> > > The problem is I get an exception in my C# code which calls this
stored
> > > procedure:
> > > System.Data.SqlClient.SqlException: User does not have permission to
> > perform
> > > this action.

> > > I can execute the following stored procedure in the same way without
any
> > > problem:
> > > EXEC SP_XML_PREPAREDOCUMENT

> > > So, what is the difference? Why am I getting an exception?

> > > Thanks,
> > > Guogang

 
 
 

1. Triggers, Stored Procedures and user permission to call an Extended Procedure

Hi,

We are using this set of extended stored procedures to manipulate files.

http://www.bramc.ru/soft/xprocFileUtils.html

We actually store documents information in a table and then a file link that
we used the extended procedures for.  When one of these records is deleted,
I need to ensure that we also delete the associated file.

My problem is that when I call the extended procedures from the DELETE
trigger, I get an error:

Server: Msg 229, Level 14, State 5, Procedure xp_FileExists, Line 25
EXECUTE permission denied on object 'xp_FileExists', database 'master',
owner 'dbo'.

I've also tried wrapping this call inside a stored procedure and then
calling the stored procedure from the trigger, but it results in the same
error message.

When I run it, all is well of course, but when a standard user does, it
dies.

Both master and the application database are owned by "SA", so is there not
a way to get this to work so the extended procedure can be called by a
normal user?

Any help / advice would be appreciated.

Thanks,
Mike

2. Why does trigger try to dereference remote server if code path not executed

3. Update Permission on Stored Procedures

4. installer trashes ODBC driver

5. Dynamic WHERE clause in Stored Procedure with permissions

6. Front Base

7. Extended Stored Procedure Permissions

8. Extended Stored Procedure, trigger

9. permissions on execute stored procedures

10. how to create stored procedure for user permissions

11. Store procedure exec permission for PUBLIC

12. Stored Procedure Permission

13. Stored procedures and permissions