i MADE A USER AS bULK aDMIN BUT HE STILL CAN'T BULK iNSERT TO A TABLE . He has dd_writer and dd_reader roles assigned in database .
What should I do to fix this . Here is the error
The current user is not the database or object owner of table 'Temp_load'. Cannot perform SET operation.does the database bulk insert flag is on?|||Keep in mind only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
Showing posts with label admin. Show all posts
Showing posts with label admin. Show all posts
Monday, March 19, 2012
Friday, February 24, 2012
BULK INSERT
Question: All of my developers (2000) use DTS to load data. However, I jus
t
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
t
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
BULK INSERT
Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
BULK INSERT
Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...
Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...
Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
Thursday, February 16, 2012
builtin\adminstrators
what is best practice with this group account?
it is installed by default, if i understand correctl, it gives anyone who is
in the server admin group, admin rights to sql server. isn't that a bad id
ea?See:
SQL Server 2000 SP3 Security Features and Best Practices
http://www.microsoft.com/technet/pr...n/sp3sec00.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
i believe have looked at this article before...however, i just reviewed it
again.
where does this article address my question specifically..am i missing it.
thanks|||Hi Kevin,
Thanks for your update. As the SQL Server 2000 security model is based on
the Windows security model and as the system administrator of windows
operating system, it will have permission on all the activities on the SQL
Server since they are combined together. So, 'buildin\admin' security will
depends on the windows security model for it. SQL Server will benifit from
the Windows security architecture. Please refer to the following article:
Authentication Modes
http://msdn.microsoft.com/library/e...curity_47u6.asp
Security Architecture
http://msdn.microsoft.com/library/e...curity_4fol.asp
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||let me put it this way, if you have os adminstrators who are in the local ad
ministrators group and you don't want them to have admin rights to the sql s
erver...then you should i remove the builtin\administrators group from the
sql server logins.....is t
hat correct?
thanks
-jason|||Yes.
The following article has a section with links to some
issues that could come up if you remove the group:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Wed, 14 Apr 2004 14:01:06 -0700, "jason" <jason_fin>
wrote:
>let me put it this way, if you have os adminstrators who are in the local administr
ators group and you don't want them to have admin rights to the sql server...then y
ou should i remove the builtin\administrators group from the sql server logins.....
is
that correct?
>thanks
>-jason|||Hi Kevin,
I just want to add some more information to your question. Please look at
the
"Step 11. SQL Server Logins, Users, and Roles" in "Securing Your Database
Server" at
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/THCMCh18.asp
That is, if you differentiate the role of domain administrator and database
administrator. You'd better remove the BUILTIN\Administrators SQL Server
login and then create a specific Windows group containing specific database
administrations added to SQL server as a server login.
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi,
I am reviewing you post. Since we have not heard from you for some time, I
wonder if you still have quesitons of the information I provided. For any
question, please feel free to post new message here and we are glad to
help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Sue and Biasong,
the reason it takes me a while to tget back is because that nospam alias acc
ount doesn't work as far as sending me notifications for replys.
thanks
-jason
it is installed by default, if i understand correctl, it gives anyone who is
in the server admin group, admin rights to sql server. isn't that a bad id
ea?See:
SQL Server 2000 SP3 Security Features and Best Practices
http://www.microsoft.com/technet/pr...n/sp3sec00.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
i believe have looked at this article before...however, i just reviewed it
again.
where does this article address my question specifically..am i missing it.
thanks|||Hi Kevin,
Thanks for your update. As the SQL Server 2000 security model is based on
the Windows security model and as the system administrator of windows
operating system, it will have permission on all the activities on the SQL
Server since they are combined together. So, 'buildin\admin' security will
depends on the windows security model for it. SQL Server will benifit from
the Windows security architecture. Please refer to the following article:
Authentication Modes
http://msdn.microsoft.com/library/e...curity_47u6.asp
Security Architecture
http://msdn.microsoft.com/library/e...curity_4fol.asp
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||let me put it this way, if you have os adminstrators who are in the local ad
ministrators group and you don't want them to have admin rights to the sql s
erver...then you should i remove the builtin\administrators group from the
sql server logins.....is t
hat correct?
thanks
-jason|||Yes.
The following article has a section with links to some
issues that could come up if you remove the group:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Wed, 14 Apr 2004 14:01:06 -0700, "jason" <jason_fin>
wrote:
>let me put it this way, if you have os adminstrators who are in the local administr
ators group and you don't want them to have admin rights to the sql server...then y
ou should i remove the builtin\administrators group from the sql server logins.....
is
that correct?
>thanks
>-jason|||Hi Kevin,
I just want to add some more information to your question. Please look at
the
"Step 11. SQL Server Logins, Users, and Roles" in "Securing Your Database
Server" at
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/THCMCh18.asp
That is, if you differentiate the role of domain administrator and database
administrator. You'd better remove the BUILTIN\Administrators SQL Server
login and then create a specific Windows group containing specific database
administrations added to SQL server as a server login.
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi,
I am reviewing you post. Since we have not heard from you for some time, I
wonder if you still have quesitons of the information I provided. For any
question, please feel free to post new message here and we are glad to
help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Sue and Biasong,
the reason it takes me a while to tget back is because that nospam alias acc
ount doesn't work as far as sending me notifications for replys.
thanks
-jason
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.
>
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.
>
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
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.
>
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.
>
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.
> >
> >
>
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]
>
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]
>
Tuesday, February 14, 2012
builtin admin - OOOPS
Lets say a dba removes the builtin admin group from the sql server in haste.
They donâ't have any other logins with sysadmin rights, and they donâ't know
the SA password. How would they get sysadmin rights back?It would be difficult to start the services using some service account if there is no service
account with sysadmin privileges. For instance, Agent refuses to start unless it is sysadmin. Worst
case, you use LocalSystem as service account, then it is more difficult since you cannot use this
interactively. In that case, I guess you are in for a rebuild of the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:4ED73AA6-A4A1-452E-877B-2135C0804FC7@.microsoft.com...
> Lets say a dba removes the builtin admin group from the sql server in haste.
> They donâ't have any other logins with sysadmin rights, and they donâ't know
> the SA password. How would they get sysadmin rights back?
They donâ't have any other logins with sysadmin rights, and they donâ't know
the SA password. How would they get sysadmin rights back?It would be difficult to start the services using some service account if there is no service
account with sysadmin privileges. For instance, Agent refuses to start unless it is sysadmin. Worst
case, you use LocalSystem as service account, then it is more difficult since you cannot use this
interactively. In that case, I guess you are in for a rebuild of the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:4ED73AA6-A4A1-452E-877B-2135C0804FC7@.microsoft.com...
> Lets say a dba removes the builtin admin group from the sql server in haste.
> They donâ't have any other logins with sysadmin rights, and they donâ't know
> the SA password. How would they get sysadmin rights back?
Subscribe to:
Posts (Atom)