| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2006-05-23 : 09:10:46
|
| I have a new userNT\newuserWhen i added him to sql i only gave him DATABASEA ....and then i tried to create new table in DATABASEB and was ...how can he do this...when i did not click DATABASEA.I found out that he is part of domain/administrator so the question is i do not understand how being part of domain/administrator overwrites the settings i have.I heard that builtin\admin ...should be removed but is this from security, server roles.....So the steps are.1....NT\newguy has registration of SQL 2....When he logs in....the builtin\admin is priorty ? Not the setting in his login.Confused on the cycle of authentication. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-23 : 10:23:48
|
builtin\administrator and members of it have sysadmin privileges, it supercedes all other permissions he may have unless he invokes an approle or he has an explicit deny on some objectsit is always best practice in my humble opinion to delete this default group  --------------------keeping it simple... |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-23 : 10:24:14
|
| Access is a set of all the groups you belong too, minus any denied access. So yes, the account will get the permissions from the builtin\administrators group in addition to his own.You can remove builtin\administrators, but, before doing that, make sure you add a login for yourself and the service account as sysadmin.-------Moo. :) |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-23 : 14:03:07
|
| On the server right now...I do not have domain\myuser set up.however the builtin\admins is there....But if i log into my computer as domain\myuser and do sql query...i cannot connect using Windows NT and do what ever i like...How would domain\myuser get this right.And how comes i do not need to add domain\myuser as a sql login at all....Hmmm |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-23 : 14:05:01
|
| Whoops i should say i can connect....... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-23 : 21:07:21
|
| the builtin\administrator corresponds to the local admin group of your sql serverso if mydomain\myuser is a member of the local admin group, then you automatically gain access to the sql server with SA privilege at that if the builtin\administrator is still existing--------------------keeping it simple... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-24 : 03:46:22
|
quote:
Originally posted by jen
the builtin\administrator corresponds to the local admin group of your sql server
+ any other login that has administration rights on that box.-------Moo. :) |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-24 : 08:29:59
|
| Bear with me not Network wizard...im just the SQL person...got team of network guys here..but they taking too long to answer me...so im going to tell them lolThis is what i haveim on active directory the domain\jackType in domain admins Members (don't see him in here) Type in domain\jack He Members of (local Domain Users) UsersWhen you say...local admin group of your sql server (where do i find this).we have the sql running as domain\sqlThanks |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-24 : 08:32:45
|
| Just looked at active dictory for sql this is member of domain users...member of local domain usersJack has member of local domain users.Is that how it works.??? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-24 : 09:45:03
|
| Check in computer manager on the sql server to see if the user is within the administrators group.-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-24 : 09:54:37
|
| not local domain user..right click mycomputer on the sql server, manage, then check the administrator group and verify if the domain user you are saying is a member--------------------keeping it simple... |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-25 : 08:42:09
|
| I found it the guy is part of the local administrator for the Server so thats why when he logs in he is using the builtin admin So i now know that how he gets in...so i can do the following:I have about 10 servers to change......and they are all clustered...and replicatedim going to remove the builtin\administrators account....from security, server roles, system administrators.I have done my research so im going to create a new account1...Create a new domain\dbstart account on Windows NT and in SQL2...In SQL Security Logins, properties ...set this to System adminstrators only.3. I have looked at the settings of the builtin\admin and i see thatthis is set SQL Security Logins, properties ...set this to System adminstrators only.and all the databases are ticked.Question: Should i set the domain\dbstart databases to be all ticked............4. Add a new SQL Security Logins called NT Authority\System In SQL Security Logins, properties ...set this to System adminstrators only. Should i tick all databases to yes in here.Anything else i should be aware of before doing thisThanks......... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-25 : 09:24:20
|
| Question: Should i set the domain\dbstart databases to be all ticked............It doesn't matter wether you do or don't.-------Moo. :) |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-25 : 10:49:49
|
| I was wondering because of the builtin\admin has all the ticks and if i remove this..does sql behind the scences need this ? accountThanks |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-25 : 21:46:55
|
| if it's sysadmin then no need to tick the databases, it's sysadminwhat you need to do is:1. create a domain account that will run the sql services if they are still in local, add this in local admin group or user group (just found out this works too), always test by restarting the services--as precaution, add your domain account and add it to sysadmin2. add that domain account into your sql server with sysadmin privilege3. remove the builtin\administrator, don't deny--> delete itdenying it will stop your sql services because the sql service account is considered a builtin\administrator (unless you didn't add it) and a deny supercedes all permissions including sysadminif that "guy" needs access, then he should define what he needs and then you add his domain account and apply the appropriate permissions--------------------keeping it simple... |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-26 : 12:00:38
|
| remove the builtin\administrator, don't deny--> delete itdenying it will stop your sql services because the sql service account is considered a builtin\administrator (unless you didn't add it) and a deny supercedes all permissions including sysadminCan i just do the Security, Server Roles...system administrators and thenremove builtin\admin from here......and leaveSecurity Logins, builtin\admin in here.Thanks.. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-27 : 01:08:08
|
they will still gain connectivity to the server and if they're smart enough and your guest accounts in the databases are enabled, they can gain permission, or public permissions have not been restrictedbut hey, it's your server and you can do whatever you want to do with it  as long as you know what you are doing then everybody's a winner --------------------keeping it simple... |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-05-28 : 08:32:16
|
| So ....you recommend just doing the remove in in sql logins server roles....or ..and deleting the account in the logins.Cheers |
|
|
|
|
builtin\admin