September 15, 2008 at 10:36 am
Hi, We have users who want to create their own indexes in SQL 2000. Question is what is the minimum security needed to allow them to do this wihthout giving them SA.
Will DDLADMIN do the trick?
September 15, 2008 at 11:05 am
I'd tell them to pass the requests to you so you can analyse the actual need for the index and run it for them.
The problem is that if they can create them, they can also delete them. The worst case scenario is that they drop a perfectly valid index without your authorization and screwing up the server's performance (which you'd then have to fix).
If the DB is correctly designed and optimized in the first place, there should not have a need for many if any of those types of requests.
September 15, 2008 at 11:29 am
I as DBA do those requests now. There is another group in the organization (non-IT) that has their own server that is linked to the Data Repository server. They are Programmers that want to create their own indexes.
I am not put in the position to justify why they should not have this permission.
Things I can think of are, capacity planning, table usage ie. an audit conpare is going on that table. Yes, dropping is another concern, they can drop and modify the vendors indexes. (not a good idea)
Will ddladmin allow create and drop index?
September 15, 2008 at 11:41 am
I ran this on a clean install of 2005 (sorry, don't have 2000 installed anywhere)
EXEC dbo.sp_dbfixedrolepermission 'DB_ddladmin'
db_ddladminAll DDL but GRANT, REVOKE, DENY
db_ddladmindbcc cleantable
db_ddladmindbcc show_statistics
db_ddladmindbcc showcontig
db_ddladminREFERENCES permission on any table
db_ddladminsp_changeobjectowner
db_ddladminsp_fulltext_column
db_ddladminsp_fulltext_table
db_ddladminsp_recompile
db_ddladminsp_rename
db_ddladminsp_tableoption
db_ddladminTRUNCATE TABLE
September 16, 2008 at 7:56 am
I may soon be in the position to justify why we (IT) should be the only one to create and drop indexes on our Data Repository. We have the non-IT reporting group made up of ex-IT people who currently create their own reports from their own server. They do however obtain data from many other sources and import it into their server database. They do have rights to add indexes on their own server.
If I give the users ddladmin they will have more than just index. they can add and drop tables and fields also. They can also delete data from tables with TRUNCATE.
I will have to explain this at a VP level. Anyone have to do this before?
September 16, 2008 at 8:07 am
I never had to enforce something like that. I was most fortunate to be able to tell them to stay off the server adn remove any and all permissions to ensure that.
I think you have strong enough reasons not to want them to have access to the servers with DDL_Admin.
One suggestion could be to create another server instance for them where they would have full permissions to screw with it... but that solution incores a lot more expanses and time to setup.
I really don't have anything else to suggest... sorry.
September 16, 2008 at 8:10 am
Yes, and it is always a fun task unless you have IT savvy management, which I have now. The key thing to remember is that they are focused on the business so, you need to make sure that your justification is focused on the business and why it would not be wise to provide permissions such as are being requested to people outside of the normal IT group that is responsible for managing the databases. Such areas of focus could be data availability, reliability, your ability to plan appropriately for storage, etc. All these things if impacted will impact the business access to the data and that is what will convince him / her that this is a bad idea.
Another aspect that you will have to consider is that the main reason people are looking to have those permissions themselves is not because the want to do it necessarily but because they don't feel that they have the proper channel to get their needs met in a timely fashion. So, you should strongly consider what kind of process you will have in place to meet the needs of these customers so that you will be able to provide them with the appropriate tuning when the need arises. That will usually take care of 90% of the management as they will see that the business need is being cared for and that the business infrastructure is being kept available and efficient for the business use.
Hopefully this makes sense.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 16, 2008 at 8:16 am
Thank you David. Yes it does make sense. I need to focus on the business impact and also present a reasonable response time to create the indexes for them.
September 17, 2008 at 2:44 am
Another viewpoint is to say that the non-IT group are running a departmental server and they can do whatever they please with it. They should have total authority and responsibility for the server, which is signed off by a senior manager.
If they kill their server, then they have responsibility to fix it. If they want IT group help, then this should be on a time & materials basis, with all work cross-charged to the other department.
If they do not want to accept this, then you can influence the service level agreement that covers IT group support. If they want premier support of their server, then they have to accept the restrictions that go with it - including no user ability to create/drop database objects. If they are prepared to put up with best efforts working hours only support then they could be allowed to do the create/drop they feel they need.
This situation is a bit like maintaining a modern car. If you always take it to professionals for servicing, you expect it to keep working and you blame the professionals when it does not. If you download your own Linux on top of the engine management system, change the tyres away from recommendations, etc, then you must accept that the professionals may not always be able to keep the car running and that the problems you have may be your own to fix.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply