[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

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.


0
krishna_harik
Asked:
krishna_harik
  • 4
  • 3
4 Solutions
 
RiteshShahCommented:
remove that user from SysAdmin fixed server role.
0
 
RiteshShahCommented:
I mean remove login from sysadmin fixed server role
0
 
krishna_harikAuthor Commented:
we removed the sysadmin, but still he is able to create new database
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RiteshShahCommented:
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
 
Guy Hengel [angelIII / a3]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
 
krishna_harikAuthor Commented:
thanks for your valuable inputs from the experts
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now