Restricting the existing user to creating new database

Hello

how to assign below permission to user who is already having db_owner permissions. like
1.deploy SSIS package
2. able to schedule jobs
3. able to reorg tables databases

according to my knowledge  
for deploy SSIS package i have given roles in MSDB to that user- DTsltduser, DTSadmin and DTSoperator.
for schdule jobs given SQL agent roles  SQL agent reader, SQL Agent user SQL agent operator
for reorg tables databases given sysadmin rights.

It's working fine. but i want to restrict from creating a new database. how ?

thanks.


krishna_harikAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
is the user in a domain or local admin group?
if yes, is there still the BUILTIN\administrator login defined in your sql server logins?
if yes => the sql login mentioned has, by default, the sysadm fixed server role
which makes the user can do anything.

you need to change at that level, accordingly
0
 
RiteshShahConnect With a Mentor Commented:
remove that user from SysAdmin fixed server role.
0
 
RiteshShahConnect With a Mentor Commented:
I mean remove login from sysadmin fixed server role
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
krishna_harikAuthor Commented:
we removed the sysadmin, but still he is able to create new database
0
 
RiteshShahConnect With a Mentor Commented:
can you tell me, what server role that user has right now? just give it only one role "public"
0
 
krishna_harikAuthor Commented:
we only given 'public' rights and added the user to DCOM Security group.
0
 
krishna_harikAuthor Commented:
Any one please help me.. it is still pending to restrict the user permission.
0
 
krishna_harikAuthor Commented:
thanks for your valuable inputs from the experts
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.