Permissions required to create a Spatial Index on a table, for this BOL says:
Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.
However, I find that the Alter permission alone is sufficient to create a Spatial Index on a table. (without having server Sysadmin rights)
The test I have created below: (run each batch to find out what is happening)
USE adventureworks;
GO
/* Start these scripts with a priveledged account */
CREATE TABLE employees
(employeedid INT PRIMARY KEY IDENTITY(1,1),
empaddress GEOGRAPHY
)
INSERT INTO employees VALUES(geography::STGeomFromText('LINESTRING(47.656 -12.360, 47.656 -12.343)', 4326))
GO
/* Create our test SQL account with limited access = no securables yet */
CREATE LOGIN LimitedPerms WITH Password = 'pass';
CREATE USER LimitedPerms FOR LOGIN LimitedPerms;
GO
EXEC AS USER='LimitedPerms';
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser'
GO
/* Should give an error = does not have any rights yet */
CREATE SPATIAL INDEX emp_spatIx on employees(empaddress);
GO
REVERT;
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser';
GO
/* Give alter permission on table: employees to our test user */
GRANT ALTER ON OBJECT::employees to LimitedPerms;
GO
EXEC AS USER='LimitedPerms';
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser';
GO
/* Allows user to create index without being a sysadmin! */
CREATE SPATIAL INDEX emp_spatIx on employees(empaddress);
GO
REVERT;
GO
Anyone care to test and report back? or inform why documented like this?
Thanks
EDIT1: This was tested on SQL Server 2008 Enterprise Edition
EDIT2: I have put feedback on Microsoft Connect
