asked on

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 .

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Atlanta_Mike

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Atlanta_Mike

Arghhh... beat as I type.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TRACEYMARY

ASKER

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

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TRACEYMARY

ASKER

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.

Guy Hengel [angelIII / a3]

yes, you can press that button.

TRACEYMARY

ASKER

Under security, server roles....system administratory
BUILTIN\administratory..............You cannot just deny you have to delete?

What effect what problems...will i occur?

TRACEYMARY

ASKER

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

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

TRACEYMARY

ASKER

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

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

TRACEYMARY

ASKER

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 .

Atlanta_Mike

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.

TRACEYMARY

ASKER

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..?

Atlanta_Mike

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.

TRACEYMARY

ASKER

Thanks every one .....and for replying quickly.