• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

NT login becomes super sa

I just set up one of our users as Windows NT login in security did not give any conditions for them..like system administrator.

Then added the sql registration onto his machine ..............
went into one of the databases and i could add a table delete table ..when i did not give access to this in the security login.

Any reasons ? why the new windows nt has super sa rights .
0
TRACEYMARY
Asked:
TRACEYMARY
  • 8
  • 4
  • 3
  • +2
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the nt login by any chance part of the domain administrators?
did you use the windows authentication when registering the sql server in his machine or did you use sa as login?
0
 
LowfatspreadCommented:
?

because he belongs to an existing NT group which has SA or DBO rights to the Database?

is the User an Admin or has admin rights ro his machine?

has you diabled the BUILTIN\ADMIN groups rights to the server?

 
0
 
Atlanta_MikeCommented:
Verify that the user is not part of the local administrators on the box. By default the BUILTIN\Administrators group is assigned to the sysadmin server role.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Atlanta_MikeCommented:
Arghhh... beat as I type.
0
 
HDatabase AdministratorCommented:
Sounds like Mixed mode authentication is turned on..

If the user current isnt a domain admin or a local admin then check the rights given to the user in sql server security.  This user may not have server admin rights but they may have full DB rights....
0
 
TRACEYMARYAuthor Commented:
Thanks all for replying quickly that is greatly appreciated.

So if the user has  domain administrators rights...........set up in active directory.
.......this basically means he has "sa" rights in sql regardless of what i set him up as...in server roles etc.

Hmm...not sure i want that..............so is it best to set up a new sql user....
what would disabling the BUILTIN\ADMIN groups rights to the server?         do i have a lot of jobs running as
Windows NT accounts.


Thanks

0
 
LowfatspreadCommented:
from a security perspective you should disable the builtin\admin group access for sql server in production
environments...

and assign each task function its own specific userid and level of access /rights as required..





 
0
 
TRACEYMARYAuthor Commented:
Bear with me..here..

By having the builtin\admin enabled
   1.   Having a user set up in Active Directory as domain administrator rights.
         will allow that user to become super sa ......
   
By denying access
   2.   Having a user set up in Active Directory as domain administrator rights.
         will allow that user only access to what i put in the server roles and what database i pick in
         security logins.

If all of our dt jobs are by one windows nt /user i should just select the security roles as system administrator and i be ok.

If so............i can deny built in....press that button.



 
         
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, you can press that button.
0
 
TRACEYMARYAuthor Commented:
Under security, server roles....system administratory
BUILTIN\administratory..............You cannot just deny you have to delete?

What effect what problems...will i occur?


0
 
TRACEYMARYAuthor Commented:
Oh i see i do not delete it .
if i go to security logins i see builtin\administrator ...here i can deny access.

Would that mean my windows nt    aac_main\sql which i use for all DTS jobs would stop working.

Thanks
0
 
HDatabase AdministratorCommented:


As well as DTS packages/jobs you need to make sure that any applications that are accessing the database are not useing the built in admin account as well..
0
 
TRACEYMARYAuthor Commented:
Is this the way to do it..
Go to security logins i see builtin\administrator ...here i can deny access....rather than just deleting it from
security, server roles, system administrators..

,,,,Will my windows nt    aac_main\sql which i use for all DTS jobs still work.


Thanks

0
 
HDatabase AdministratorCommented:
It all depends.  Is your windows NT account setup with full rights in the SQL security or is it accessing the db through the built in DB account like the other profile that you were questioning originally.  Just make sure your NT account that your running your jobs under has DB owner for the datbase and you should be all set...
0
 
TRACEYMARYAuthor Commented:
Bear with me................

Active Directory              Rights                      
aac/sql                           domain administrators
aac/newguy                    domain administrators

Now if the NT Guy insists on domain administrator rights i have to security them in SQL by

Inside of SQL Security Logins
                                  server roles
aac/sql                               system administrator ticked             (is the sa should have all rights)
aac/newguy                        not ticked
builtin/administrator            (is ticked
                                           all databases are ticked also)
                 
So unticking system administrator for builtin/administrator will not then prevent  aac/newguy access to any databases....unless under his login and databases i click them.
                   

Is that the correct understanding.

Is that how you all have it .
         
0
 
Atlanta_MikeCommented:
You need to remove the builtin\administrator rights and that will prevent them from having access. aac\newguy appears to also be restricted. As long as they don't have the aac\sql password you'll be ok.

0
 
TRACEYMARYAuthor Commented:
Sorry for all questions everyone....just don't want to do anything silly here.
This company had dba before....and im surprised at some things....i only been here 3 weeks.


You need to remove the builtin\administrator rights
                   Do this by going to  security,  logins , builtin\administrator and deny access.
                 
rather than remove it in.
Security, System Administrators, BUILTIN\Administrators  -  remove.

Which is best way to do it........
I want to be able to put back if ...something don't work..?


                   

0
 
Atlanta_MikeCommented:
Denying it will leave it in the database so you can just uncheck the deny box. That will work. Especially if you ever think you'll need to give quick access.
0
 
TRACEYMARYAuthor Commented:
Thanks every one .....and for replying quickly.

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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