Bulk Insert -- Access denied issues - 2

Hi All

Same situation as described here , same issue.

SQL Server(SQL2005 on Windows2003) uses domain account. This domain account enabled to be trusted for delegation. Client connects to server using Windows auth. Client issues BULK INSERT with UNC path. Statement returns error:

Cannot bulk load because the file "\\Server\pub\file.txt" could not be opened. Operating system error code 5(Access is denied.)

SQL 2000 runs this statement successfully so statement and file are OK. Everyone has all permissions on network share. Domain account granted all permissions explicitly so there is no access troubles.

Audit show anonymous connections.

Question is - how to put delegation in work?

Thank you in advance,

Alexander Sinitsin

I don't think this is a problem with delegation. How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication? If it is Windows authentication then the service account credentials will not be used (change from SQL Server 2000) so the connected user / login needs to have the permissions to access the file. For SQL logins, the service account credentials will be used. So could you please describe your scenario?

1. What is the SQL Server service account?

2. Where is the data file located?

3. How does the user connect to SQL Server? SQL or Windows Auth?

4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

|||

Hi Umachandar Jayachandran!

Thanks for your answer.

>How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication?

Client connected using Windows authentication.

>service account credentials will not be used (change from SQL Server 2000)

Sure. Server should use client's credential to access file.

>so the connected user / login needs to have the permissions to access the file.

Connected user has full permissions to access the file. Even more, this user is a file owner-creator.

>1. What is the SQL Server service account?

SQL Server run under domain account. This domain account enabled to be trusted for delegation.

>2. Where is the data file located?

Data file located on some PC in network. Not on SQL Server locally.

>3. How does the user connect to SQL Server? SQL or Windows Auth?

Windows Auth.

>4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

Everybody has all permissions on the data file. Client connected has all permissions. Even SQL Server's domain account has all permissions. I can access this file under client's credentials without any problems.

As far as I can see, in this scenario SQL Server 2005 should use client credentials to access file. But when I run BULK INSERT, access audit show anonymous access, not client's access.

Best regards,

Alexander Sinitsin

builtin\administrators login and database access

How do I remove db_owner access from the
builtin\administrators sql login?
The db_owner and sysadmin roles have been assigned to the
windows domain account that starts SQL Server and the sa
login for all db's.
I need to remove this for secutiry/legal reason.
Any problems if I just remove the builtin\administrators
login?
Thanks."Matt Troska" <matt.troska@.co.mcleod.mn.us> wrote in message
news:495f01c3e42f$efd44e50$a501280a@.phx.gbl...

quote:

---

> How do I remove db_owner access from the
> builtin\administrators sql login?

---

You don't want to do that...

quote:

---

> The db_owner and sysadmin roles have been assigned to the
> windows domain account that starts SQL Server and the sa
> login for all db's.
> I need to remove this for secutiry/legal reason.
> Any problems if I just remove the builtin\administrators
> login?

---

That's the correct approach, as long as you've granted another group/user
sysadmin equivalence, then you can safely delete the builtin\administrators
group.
After I install SQL Server and setup the security, deleting the
builtin\administrators group is usually one of the first steps I take.
Steve

Builtin\Administrators

I have a rogue co-worker who wants to support SQL buit doesn't know
how. This person is a domain admin so it's difficut to keep him from
accessing and "doing stuff" with SQL server.
I want to SAFELY deny access to the builtin\administrator login but I
want to be sure I don't deny access to people who need it.
Here are a few steps I think I nned to take in order to secure my
instance:
1. Review the local administrators group membership on the server and
remove any unnecesary members.
2. Create a local group and add the network admins that need access to
SQL to the group
3. Create a role/user (not sure which is best) and map the local group
to this login
4. Restrict login based on fixed server/database roles
Any asistance is appreciated.
I always remove BUILTIN\Administrators not only from sysadmin but from the
entire SQL Server instance. If there are some services, like FullText Search,
that may be using these permissions, I give them a Windows account instead.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"NC3" wrote:

> I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>
|||While I respect the advice given and acknowledge you need to do those
things, frankly you have a people problem, not a database problem.
I suggest that if he is doing things that has gotten your attention, and
won't stop, you need to take it up with your manager. If you don't and just
deny him access, it is likely HE will take it up wit HIS manager and then,
you'll have a completly different mess.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1194973802.571321.51230@.o3g2000hsb.googlegrou ps.com...
>I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>

Builtin\Administrators

I have a rogue co-worker who wants to support SQL buit doesn't know
how. This person is a domain admin so it's difficut to keep him from
accessing and "doing stuff" with SQL server.
I want to SAFELY deny access to the builtin\administrator login but I
want to be sure I don't deny access to people who need it.
Here are a few steps I think I nned to take in order to secure my
instance:
1. Review the local administrators group membership on the server and
remove any unnecesary members.
2. Create a local group and add the network admins that need access to
SQL to the group
3. Create a role/user (not sure which is best) and map the local group
to this login
4. Restrict login based on fixed server/database roles
Any asistance is appreciated.I always remove BUILTIN\Administrators not only from sysadmin but from the
entire SQL Server instance. If there are some services, like FullText Search,
that may be using these permissions, I give them a Windows account instead.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"NC3" wrote:
> I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>|||Hello!
Local Administrators Windows group has nothing to do with Domain Admins
group. They are different stuff. As your co-worker is in the Domain Admins
group (according to your saying he was a domain admin) and the mentioned
group must be a member of the sysadmin fixed server role. So it would not
make any difference if you remove Local Admin group in SQL Server Windows
Logins. He would be able to connect to your instance (I assume there is a
Domain Admins login in SQL Server Logins)
If your SQL Server is in a domain environment, then you better use domain
accounts to connect to your instance. BUILTIN\Administrators should be
deleted in many cases however this rule does not apply to ALL cases. First,
ensure that there is nothing using this account related to your SQL Server
instance. After ensuring this, you can delete it safely.
Why don't you explicitly deny him connecting to your SQL Server if he is the
only problem?
In which group is your account? You can remove Domain Admins from the Logins
if you are not in that group and if there is no one connecting to your SQL
Server in that group.
--
Ekrem Önsoy
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1194973802.571321.51230@.o3g2000hsb.googlegroups.com...
>I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>|||While I respect the advice given and acknowledge you need to do those
things, frankly you have a people problem, not a database problem.
I suggest that if he is doing things that has gotten your attention, and
won't stop, you need to take it up with your manager. If you don't and just
deny him access, it is likely HE will take it up wit HIS manager and then,
you'll have a completly different mess.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1194973802.571321.51230@.o3g2000hsb.googlegroups.com...
>I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>

BUILTIN\Administrators

Hi,
I must remove BUILTIN\Administrators. Should i create 2 domain accounts, one for each service (MSSQLServer, SQL Server Agent) or one for the two services ' actually i'm using LOCAL SYSTEM ACCOUNT.
If i use 2 accounts i must give sysadmin for the two accounts, and i don't use the account sa, i have one other account with sysadmin permiss=F5es for my day by day work. When i use MBSA, i always get the message that i have more than 2 sysadmins. How can i troubleshoot this question '
What is your approach of remove BUILTIN\Administrators and start the accounts with domain non admin accounts ?
My approach:
-Remove BUILTIN\Administrators
-create one domain account with regular priviledges
-assign the account for the two services
-grant SQLServer sysadmin rights on the account
Can anyone give a nice approach for this question ? issues regarding the change ?
Thanks in advance for the help
Miguel
MCPI would use 1 domain account... The necessary permissions can be found in
books on line. Search for "service accounts"
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miguel" <anonymous@.discussions.microsoft.com> wrote in message
news:05fc01c3db7f$f6c96870$a001280a@.phx.gbl...
Hi,
I must remove BUILTIN\Administrators. Should i create 2
domain accounts, one for each service (MSSQLServer, SQL
Server Agent) or one for the two services ' actually i'm
using LOCAL SYSTEM ACCOUNT.
If i use 2 accounts i must give sysadmin for the two
accounts, and i don't use the account sa, i have one other
account with sysadmin permissões for my day by day work.
When i use MBSA, i always get the message that i have more
than 2 sysadmins. How can i troubleshoot this question '
What is your approach of remove BUILTIN\Administrators and
start the accounts with domain non admin accounts ?
My approach:
-Remove BUILTIN\Administrators
-create one domain account with regular priviledges
-assign the account for the two services
-grant SQLServer sysadmin rights on the account
Can anyone give a nice approach for this question ?
issues regarding the change ?
Thanks in advance for the help
Miguel
MCP

Builtin\Administrators

I have a rogue co-worker who wants to support SQL buit doesn't know
how. This person is a domain admin so it's difficut to keep him from
accessing and "doing stuff" with SQL server.
I want to SAFELY deny access to the builtin\administrator login but I
want to be sure I don't deny access to people who need it.
Here are a few steps I think I nned to take in order to secure my
instance:
1. Review the local administrators group membership on the server and
remove any unnecesary members.
2. Create a local group and add the network admins that need access to
SQL to the group
3. Create a role/user (not sure which is best) and map the local group
to this login
4. Restrict login based on fixed server/database roles
Any asistance is appreciated.I always remove BUILTIN\Administrators not only from sysadmin but from the
entire SQL Server instance. If there are some services, like FullText Search
,
that may be using these permissions, I give them a Windows account instead.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"NC3" wrote:

> I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>|||Hello!
Local Administrators Windows group has nothing to do with Domain Admins
group. They are different stuff. As your co-worker is in the Domain Admins
group (according to your saying he was a domain admin) and the mentioned
group must be a member of the sysadmin fixed server role. So it would not
make any difference if you remove Local Admin group in SQL Server Windows
Logins. He would be able to connect to your instance (I assume there is a
Domain Admins login in SQL Server Logins)
If your SQL Server is in a domain environment, then you better use domain
accounts to connect to your instance. BUILTIN\Administrators should be
deleted in many cases however this rule does not apply to ALL cases. First,
ensure that there is nothing using this account related to your SQL Server
instance. After ensuring this, you can delete it safely.
Why don't you explicitly deny him connecting to your SQL Server if he is the
only problem?
In which group is your account? You can remove Domain Admins from the Logins
if you are not in that group and if there is no one connecting to your SQL
Server in that group.
Ekrem nsoy
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1194973802.571321.51230@.o3g2000hsb.googlegroups.com...
>I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>|||While I respect the advice given and acknowledge you need to do those
things, frankly you have a people problem, not a database problem.
I suggest that if he is doing things that has gotten your attention, and
won't stop, you need to take it up with your manager. If you don't and just
deny him access, it is likely HE will take it up wit HIS manager and then,
you'll have a completly different mess.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1194973802.571321.51230@.o3g2000hsb.googlegroups.com...
>I have a rogue co-worker who wants to support SQL buit doesn't know
> how. This person is a domain admin so it's difficut to keep him from
> accessing and "doing stuff" with SQL server.
> I want to SAFELY deny access to the builtin\administrator login but I
> want to be sure I don't deny access to people who need it.
> Here are a few steps I think I nned to take in order to secure my
> instance:
> 1. Review the local administrators group membership on the server and
> remove any unnecesary members.
> 2. Create a local group and add the network admins that need access to
> SQL to the group
> 3. Create a role/user (not sure which is best) and map the local group
> to this login
> 4. Restrict login based on fixed server/database roles
> Any asistance is appreciated.
>

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.

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.
I suggest you start with creating a new windows group, ad appropriate windows users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course. :-)
One thing I know can be a problem is full-text search. I think that "local system" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. That should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I suggest you Google the
archives for a bit more info on that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of those
> people. What I am planning to do is take builtin/administrator login out of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>
|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/default...b;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/default...b;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>
|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/default...b;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/default...b;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> those
> of
it.
>

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.I suggest you start with creating a new windows group, ad appropriate windows users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course. :-)
One thing I know can be a problem is full-text search. I think that "local system" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. That should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I suggest you Google the
archives for a bit more info on that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of those
> people. What I am planning to do is take builtin/administrator login out of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/default.aspx?scid=kb;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> > Hi, our sql box has too many people (including sql service account) with
> > local admin rights, some even have domain admin rights. I am trying to
> > tighten the security on those boxes and decrease the security level of
> those
> > people. What I am planning to do is take builtin/administrator login out
> of
> > the sql box, and add sql services account back and grant SA rights to
it.
> > Does anyone see any problem with this approach? Thanks.
> >
> >
>

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.I suggest you start with creating a new windows group, ad appropriate window
s users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course.
:-)
One thing I know can be a problem is full-text search. I think that "local s
ystem" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. T
hat should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I
suggest you Google the
archives for a bit more info on that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol
=seagreen]
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of tho
se
> people. What I am planning to do is take builtin/administrator login out o
f
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>[/vbcol]|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/defaul...kb;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/defaul...kb;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccoun
t]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/defaul...kb;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/defaul...kb;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTacco
unt]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> those
> of
it.[vbcol=seagreen]
>