Solved

Restricting the existing user to creating new database

Posted on 2009-07-10
8
564 Views
Last Modified: 2013-11-10
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
Comment
Question by:krishna_harik
  • 4
  • 3
8 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 300 total points
ID: 24821563
remove that user from SysAdmin fixed server role.
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 300 total points
ID: 24821567
I mean remove login from sysadmin fixed server role
0
 

Author Comment

by:krishna_harik
ID: 24821666
we removed the sysadmin, but still he is able to create new database
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 300 total points
ID: 24821686
can you tell me, what server role that user has right now? just give it only one role "public"
0
 

Author Comment

by:krishna_harik
ID: 24822213
we only given 'public' rights and added the user to DCOM Security group.
0
 

Author Comment

by:krishna_harik
ID: 24837560
Any one please help me.. it is still pending to restrict the user permission.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 24995457
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
 

Author Closing Comment

by:krishna_harik
ID: 31601994
thanks for your valuable inputs from the experts
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question