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
Best Answer
Microsoft connect ticket was resolved with wording changed:
Please refer to link below (changes have not been made on BOL yet, 1 dec)
https://connect.microsoft.com/SQLServer/feedback/details/706766/books-online-suggested-permission-to-create-spatial-index-incorrect