builtin\admin

Our servers are in mixed mode.

I have about 10 Window NT accounts.

if i log in domain\myacocunt into windows NT then i bring up SQL Query and do connect with Windows NT i can do what ever i like inside of sql i can delete add etc just like being the sa.

We have the builtin\admin enabled.

The question is i thought i had to have domain\myaccount in sql server logins regardless so that it goes windows nt authentication then sql authentication but it looks like i don't.

In order to take control....i need to have domain\myaccount only access DatabaseA

So to get this working.........do i just remove builtin\admin from security, server roles, system adminsitration......

Also i would like to know why not having a nt login inside can allow user to do what they like.

If you are a local administrator, you will get SQL Server access by virtue of being a member of Builtin\Administrators. It is sufficient for a group you are a member of to be granted access to SQL Server, for you to get access as well. This allows administrators to grant access to a Windows group instead of individually granting access to each group member.

Removing the Builtin\Administrators login will prevent local administrators from getting in, unless a login was created for them or some group they belonged to was given access. But note that a local administrator can always connect, if he can start the server in single-user mode - this is allowed to prevent an administrator from locking himself out.

Thanks
Laurentiu

|||

Thanks for replying.

So if i put the new login domain/startupsql as the administrator on the SQL server.

In sql server i create a new login domain/startupsql add server role of system administrator and then

remove builtin/admin from security server roles system administrator...do i have to delete from security logins or ok to leave here if done remove above.

So the start up will be domain/startupsql.

Do i change the properties on EM or can i stop and start by chainging connections in windows ....services.

Is that about all i have to do.........do you know what this NT Authority server account is and why i need that and what are the server roles.

When i view details of builtin/adimin it have evey server roles selected and every database do i need to do that with the new domain/startupsql

|||

I'm not sure I understand what you are trying to do.

You can remove builtin\administrators if you don't want other local administrators to connect.

Do you want to change the service account as well? I don't understand what you mean by "So the start up will be domain/startupsql."

The NT Authority account is most likely the entry for your current service account. Server roles are explained here: http://msdn2.microsoft.com/en-US/library/ms188659.aspx.

Thanks
Laurentiu

|||

Right now all our server administrators can go into SQL and do everything...so i need to remove builtin/administrators from security, server roles, system administrators and remove builtin

From what i understand is that...when you click on SERVER properites in EM under, Security you have start up service account and it should not be the system account but be a account that is a domain/newaccount to start up SQL.

I am wondering what else i need to be aware of before turning this off...i read it may not start SQl and such..

Thanks

|||

If you don't want to have administrators accessing your server, just remove the Builtin\Administrators builtin. This will prevent direct access to the server. You don't need to worry about server roles, just removing the group with "DROP LOGIN [Builtin\Administrators]" is sufficient.

The service account is a different thing - it's the account under which the SQL Server service is running. Configuring this is external to SQL Server and is unrelated to whether you have or not the Builtin\Administrators login present.

Of course, you should try all these ideas in a test environment until you're happy with the result, before attempting to do configuration changes on your main system.

I assume you're using SQL Server 2000 - if that's the case, note that if you remove Builtin\Administrators and you have no other sysadmin access, you may lock yourself out of the server.

Thanks
Laurentiu

|||

drop...u mean just go to security, server roles, system administrations and do remove buitlin/administrators.....does sql need this for anything else...do i need to replace it with a new login ? what does this do except allow the administrators of windows to do use sql like sa.

Yes the part u mention re to sysadmin access....- i have set up the domain/sql account as system administrators and myself aswell...do i need a just a regular sql account that has security, server roles, system administrators flag set aswell.

I was under the impression in the server properties the start up settings that it should not be a system account (not sure what system account the default sql uses) but i need to change that to domain/sql.

how is your server configured.