[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

NT login becomes super sa

Posted on 2006-05-22
19
Medium Priority
?
263 Views
Last Modified: 2008-02-26
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
Comment
Question by:TRACEYMARY
  • 8
  • 4
  • 3
  • +2
19 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 16736173
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 800 total points
ID: 16736186
?

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
 
LVL 13

Assisted Solution

by:Atlanta_Mike
Atlanta_Mike earned 400 total points
ID: 16736211
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16736219
Arghhh... beat as I type.
0
 
LVL 8

Assisted Solution

by:H
H earned 400 total points
ID: 16736363
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16736491
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 800 total points
ID: 16736579
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16736641
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16736665
yes, you can press that button.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16736774
Under security, server roles....system administratory
BUILTIN\administratory..............You cannot just deny you have to delete?

What effect what problems...will i occur?


0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16736838
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
 
LVL 8

Expert Comment

by:H
ID: 16736921


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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16737001
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
 
LVL 8

Expert Comment

by:H
ID: 16737040
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16737159
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16737591
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16737661
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16737772
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16741626
Thanks every one .....and for replying quickly.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

868 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