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?
EDIT1: This was tested on SQL Server 2008 Enterprise Edition
EDIT2: I have put feedback on Microsoft Connect