Sql-server – Permission to create Spatial Index

indexspatialsql server

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