Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
They've fixed BOL says your Connect ticket. Well done! –  gbn Nov 30 '11 at 13:12
    
@VDMT If you were to self-post an answer, and self-accept, that would be SUPERB. –  jcolebrand Nov 30 '11 at 14:05
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.