question

ebzm avatar image
ebzm asked

Granting permission

What are the pros and cons of granting alter trace and bulks insert admin permission to individuals who are not dba's. Any advice? I would highly appreciate it.
sql-server-2008sql
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you give us any more information as to why it's necessary? By that I mean that there are perfectly good reasons to grant the permissions, but you shouldn't be granting (any) permission without knowing why you need to do it. What is the level of knowledge of the individuals concerned? Are these production servers or test / dev servers?
1 Like 1 ·
This is production.
0 Likes 0 ·

1 Answer

· Write an Answer
TimothyAWiseman avatar image
TimothyAWiseman answered
There can be many valid reasons why someone who is not an administrator would have valid rason to have permissions to do all types of inserts and bulk inserts so it is often quite reasonable to grant that permission. Someone involved in ETL where there is not a program which will handle it for them will likely need that type of permission. Obviously this lets that person do inserts on the database with all that implies, and how dangerous that ability may be all depends on how the database is used. On the other hand, I would find it at least unusual for someone other than a DBA to need to be able to create a trace. If someone outside my DBA team had a need to view one on infrequent occassions I would probably be inclined to have someone on the DBA team generate it for thema nd then give it to them. A developer or QA person might have a reason to need a trace on a regular basis and then I would probably give them permission. But we try to keep our developers and QA team working primarily on the development and testing servers, where I am quite happy to hand out full admin permissions to at least the senior developers and QA team members. As to the specific risks involved, having the ability to run a trace can give you vast amounts of information on what is happening on that server, not to mention traces can consume non-trivial amounts of resources so they can accidentaly slow a server down. Again how dangerous that is in practice depends on the server and how much they already know about the server.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.