create drop index ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

create drop index ?

Author

Topic

Mike_h
Starting Member

7 Posts

Posted - 2008-11-18 : 02:48:17

Hi. I have a stored procedure in which i drop an index on a table. Do a bulk insert of data and re-create the index. My problem is that certain users who need to execute the procedure can not as they are part of a group that only has data_reader and data_writer permissions. Is there any way i can force the stored proc to all the dropping and creating of an index purely within the stored procedure object without having to upgrade the users permissions.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 03:15:11

have a look at EXECUTE AS option in CREATE PROCEDURE syntax in books online
http://msdn.microsoft.com/en-us/library/ms187926(SQL.90).aspx

Mike_h
Starting Member

7 Posts

Posted - 2008-11-18 : 03:24:23

I am using SQL Server 2000.. is this option only available with 2005 onwards. If so are there any other options?

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-18 : 04:11:26

Some tips for you from BOL -

Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:

ALTER TABLE

CREATE INDEX

CREATE TABLE

All DBCC statements

DROP TABLE

DROP INDEX

TRUNCATE TABLE

UPDATE STATISTICS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 04:36:30

quote:
Originally posted by Mike_h

I am using SQL Server 2000.. is this option only available with 2005 onwards. If so are there any other options?

But you've posted this in 2005 forum

Mike_h
Starting Member

7 Posts

Posted - 2008-11-18 : 04:51:33

so i have.. apologies.. did not realise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources