Can I prevent some of users with sysadmin role to create table?

Question

Post reply

Can I prevent some of users with sysadmin role to create table?

Ognjen Kovacevic

SSC-Addicted

Points: 496
More actions
July 27, 2005 at 4:15 am

#90286

Hello,

Because there is no other way to use profiler except to have sysadmin fixed role, I have to give this role to one account for use for developers. But, I also wan to prevent then to use this login for anything else, including create objects and execute sp!! Is there any way to do this?

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Momak Say Hey Kid Points: 687 More actions · Answer 1

Momak

Say Hey Kid

Points: 687

July 27, 2005 at 4:19 am

#577481

I think not

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

I guess the real question is....

WHY are you allowing DEVELOPMENT to run PROFILER (which if done incorrectly can bring your server to a halt) on PRODUCTION (Assumption)?????

I would want to be right there doing the profiler on MY PROD server....

1. You know who is going to get called when the system starts getting slow

2. You know how to set-up profiler properly

3. BAD juju allowing DEV to run amok....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Ognjen Kovacevic SSC-Addicted Points: 496 More actions · Answer 3

Unfortunately, I HAVE TO allow developers to run profiler for maintenance purpose (for several applications) on production server. That is the way it is. Thanks, anyway.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

What you can do is run Profiler and have it put the information into a trace table or trace file (with rollover). Then give the developers access to the trace table or trace file location. This allows them to get Profiler data in almost real time without having those kinds of rights on your SQL Server.

K. Brian Kelley
@kbriankelley

Ognjen Kovacevic SSC-Addicted Points: 496 More actions · Answer 5

Cool,

It is nice solution, and with some modification, it can be usefully.

Of course, it will be better if they can run profiler with their custom settings, but it is good enough for now.

Thanks a lot.

barsuk SSChampion Points: 12280 More actions · Answer 6

Our users once in a while need to make changes to the production. So if their mamager approves that, they call/mail DBA with ticket number.

We execute a process which adds that person to a Special Role on a server ( with sysadminn rights) and fire a profiler which collects all the info on that developer. Once changes are made, we revoke his access to that Special Role and stop the trace. Auditors/managers can always review the trace.