I have a few proggramers in my firm. They connect to server with login 'prog' (not 'sa'). I don't want to give them sysadmin rights, because I don't want to allow them to CREATE, ALTER, DROP, DETACH, ATACH, BACKUP, RESTORE ... DATABASE, and some other things (sp_addlogin, sp_password, ...).
They have db_owner role for one of few database we have, with all permissions on the objects of that database. But, they can not use SQL-Profiler wich is a poweful for them to manage theirs applications, because they are not member of sysadmin role.
How can I avoid that.
Can I give them sysadmin rights, but to exclude some privileges.
Or, what to add, to be able to use SQL Profiler, without sysadmin rights
1. Open Enterprise manager.
2. Click on the server name -> Management
3. Right Click on SQL Server Agent
4. Choose the Job System tab
5. Uncheck the box that says "Only users with sysadmin privleges can execute CMdExec and ActiveScripting job steps.
6. You will be prompted to enter log in information - enter the system account info you want to use (Domain or Local) to run scripts - it should be at least a local admin account for the box, I normally use the same logon as that used by the MSSQL service.
1. Open Administrative tools (You will need admin rights on the server to do this)
2. Go to Local Security Policy (this can also be done at the domain level, speak to your LAN admin about how they would like this setup)
3. Click on "user rights Assignment" in the left hand pane.
4. In the right hand pane, scroll down until you find "replace a process level token".
5. Double click on "Replace a Process level token", and add the proxy user account (Step 6 above).
6. Apply the change. You may have to restart the MSSQL and SQL Server Agent services for the change to take place, I am not sure.
And since your developers are not in the sysadmin group you as the person that is located in the sysadmin group will need to create the proxy account.
You may need to investiagate what rights you can give to the proxy account.