I took over a database and our analysts have db_owner rights to it. They were having performance issues and in troubleshooting it I saw they created an index that was incorrect. I am not a fan of them creating indexes so want to remove that priv. I don't really want to take away DB_OWNER yet because I don't know what all would happen with apps and jobs if I did that. Is there a way to just remove the Index creation rights from a user?
Permissions required for CREATE INDEX, from this MSDN page
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.
You could try to revoke the alter permission privilege on all tables, but that is table-specific, so if/when tables are added you will need to make sure that you revoke the permissions. The other option, as you have indicated is to take away the db_owner role.
The generally recommended practice is that users have only the essential privileges required to do their work. Giving db_owner role is the easy way out, and I have done that myself and kicked myself for it. Even if it is a bit painful, see if you can remove db_owner permission. You can grant them execute privileges on stored procs, and if required read privileges on the tables (db_datareader database role).
The ideal way to manage this would be to create a role for analysts and grant required permissions (and only required permissions) to that role. Then add the analysts to that role.
Yeah, I am not a fan of taking over things and finding these types of things Though I too have done the db_owner on occasion (seriously who hasn't at one point). It just scares me to take that away at this point and see what all breaks.
Would it be possible to do a trigger on table creation to alter permission on that table?
There are a number of ways to make a table read-only, many of them hacks (in my opinion of course). But some of the do sound very clever, I must admit. See here for a few different ways.
I mean they do need to be able to do more then just read data, so not sure I want to read only all the tables.
Such a pain in the you know what at this piont.
I agree. I have a very similar situation. I, as the DBA, just want to deny developers the capability to create / alter / delete indexes. Anything else they can still do. But I can't find any way in SQL Server to do this except a DDL trigger, but that's an after trigger, forcing a rollback rather than preventing the command, so it creates huge overhead.
you could use Audit to send nastygrams and try to engineer proper behavior
1 Like