SQL,access denied: February 2012

Saturday, February 25, 2012

account used to run SQL Server services

I'm attempting to follow the best practice of running the SQL Server
services under a local windows account with "just enough priviledges"
without assigning it as an admin. I've read numerous places to not use the
Local System Account as well.
In Books Online, the section titled "Troubleshooting MSSQLServer or
SQLServerAgent Services User Accounts" has the list of rights the account
must have to make the services work. Is there a utility that you can point
at a local windows account, and it will assign the appropriate rights for
you? I missed my opportunity to do this when installing SQL Server
initially.
Thanks in advance.
MarkI don't know of a utilty.
You may find these links helpful:
http://whidbey.msdn.microsoft.com/l...erview_6k1f.asp
http://whidbey.msdn.microsoft.com/l...nstall_4p0z.asp
Keith
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:%238GNYFk$EHA.3924@.TK2MSFTNGP15.phx.gbl...
> I'm attempting to follow the best practice of running the SQL Server
> services under a local windows account with "just enough priviledges"
> without assigning it as an admin. I've read numerous places to not use
the
> Local System Account as well.
> In Books Online, the section titled "Troubleshooting MSSQLServer or
> SQLServerAgent Services User Accounts" has the list of rights the account
> must have to make the services work. Is there a utility that you can
point
> at a local windows account, and it will assign the appropriate rights for
> you? I missed my opportunity to do this when installing SQL Server
> initially.
> Thanks in advance.
> Mark
>

Account used for sql 2005, x64, " lock pages in memory" option

Our sql 2005, x64, box, has 9GB of RAM and the sql server account is the "local system account". I opened the group policy editor to give that account "lock pages in memory" permission but I couldn't find that account in the "Add User or Group" pane of the policy editor. I did find something called "local service group" though it does not appear to exist as a local group or user on the box.

1. Do I need to create a domain user for this purpose?

2. Do the other services (e.g. Analysis Services, Integration Services, etc) also need to run under an account that has "lock pages in memory" privelage?

TIA,

barkingdog

Hey Dog,

I assume you want to do this because you are going to enable AWE?

1. You should not have to add Local System to this Policy because Local System pretty much owns the server, but you can add it by double clicking the policy>add user> and then type in SYSTEM and click OK. Make sure the Locations box is pointing to your local server and not to the domain when you do this.

2. I'm not sure but if they are both running under Local System as well then you should be covered by performing step 1.

|||

Andy,

Thanks, didn't know that stuff.

Also from what I understand (from Slava's blog) there is no need to enable AWE on an x64 machine with 64-bit OS and 64-bit sql 2005. (If you are interested I can probably find the URL to Slava's blog

Barkingdog

|||

Hey dog,

Yeah I do seem to remember that discussion but for some reason I remain skeptical

The reason I say that is I have an x64 server with 16GB of RAM and just for kicks I enabled AWE at 6144MB of RAM and after a month of running the server, SQL Server has never consumed more than 6GB of RAM, hhhmmm..

I'll have to do some more testing to see what the scoop is.

Account under SQL Server

How do I know which account under which the SQL Server 2000 runs ?On Enterprise Manager, select your server, right-click, Properties and
Security tab.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> How do I know which account under which the SQL Server 2000 runs ?
>
>|||Hi Ben,
I am asking this question because I still stuck in my full-text search
problem.
I tried to do many thing to get around it like to change user account to
start up SQLServer.
I just posted a thread regarding changing the account start up the
SQLServer, if you can help me that would be great.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> On Enterprise Manager, select your server, right-click, Properties and
> Security tab.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > How do I know which account under which the SQL Server 2000 runs ?
> >
> >
> >|||Full-Text Search is another Windows service. What is the problem?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> Hi Ben,
> I am asking this question because I still stuck in my full-text search
> problem.
> I tried to do many thing to get around it like to change user account to
> start up SQLServer.
> I just posted a thread regarding changing the account start up the
> SQLServer, if you can help me that would be great.
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> >
> > On Enterprise Manager, select your server, right-click, Properties and
> > Security tab.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > How do I know which account under which the SQL Server 2000 runs ?
> > >
> > >
> > >
>
>|||I created a catalog in SQL Server 2000 but cannot get results from this
catalog by issuing the SQL with CONTAINS. It just return empty set with
headers only.
The status of the catalog is always idle even after full population.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> Full-Text Search is another Windows service. What is the problem?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Hi Ben,
> >
> > I am asking this question because I still stuck in my full-text search
> > problem.
> > I tried to do many thing to get around it like to change user account to
> > start up SQLServer.
> > I just posted a thread regarding changing the account start up the
> > SQLServer, if you can help me that would be great.
> >
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > >
> > > On Enterprise Manager, select your server, right-click, Properties and
> > > Security tab.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > How do I know which account under which the SQL Server 2000 runs ?
> > > >
> > > >
> > > >
> >
> >
> >|||Probably Full-Text Search is running as System Local and has no permissions
to SQL Server. Try running the Full-Text Search using the same service
account as SQL Server. This time use the Control Panel / Services applet. The
service must be named MSSearch or something similar.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> I created a catalog in SQL Server 2000 but cannot get results from this
> catalog by issuing the SQL with CONTAINS. It just return empty set with
> headers only.
> The status of the catalog is always idle even after full population.
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> >
> > Full-Text Search is another Windows service. What is the problem?
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > Hi Ben,
> > >
> > > I am asking this question because I still stuck in my full-text search
> > > problem.
> > > I tried to do many thing to get around it like to change user account to
> > > start up SQLServer.
> > > I just posted a thread regarding changing the account start up the
> > > SQLServer, if you can help me that would be great.
> > >
> > >
> > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > >
> > > > On Enterprise Manager, select your server, right-click, Properties and
> > > > Security tab.
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > How do I know which account under which the SQL Server 2000 runs ?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hi Ben,
I go to EM, right click my server instance, select the "Edit Registered SQL
Server Properties" knowing that I checked the
"Use SQL Server authentication".
Am I going to use that account in the Control Panel - Services ?
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> Probably Full-Text Search is running as System Local and has no
permissions
> to SQL Server. Try running the Full-Text Search using the same service
> account as SQL Server. This time use the Control Panel / Services applet.
The
> service must be named MSSearch or something similar.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > I created a catalog in SQL Server 2000 but cannot get results from this
> > catalog by issuing the SQL with CONTAINS. It just return empty set with
> > headers only.
> > The status of the catalog is always idle even after full population.
> >
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > >
> > > Full-Text Search is another Windows service. What is the problem?
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > Hi Ben,
> > > >
> > > > I am asking this question because I still stuck in my full-text
search
> > > > problem.
> > > > I tried to do many thing to get around it like to change user
account to
> > > > start up SQLServer.
> > > > I just posted a thread regarding changing the account start up the
> > > > SQLServer, if you can help me that would be great.
> > > >
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > >
> > > > > On Enterprise Manager, select your server, right-click, Properties
and
> > > > > Security tab.
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "Alan" wrote:
> > > > >
> > > > > > How do I know which account under which the SQL Server 2000 runs
?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Right-click the server and select 'Properties', follow the instructions I
already sent.
Do not select 'Edit SQL Server Registration Properties'.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> Hi Ben,
> I go to EM, right click my server instance, select the "Edit Registered SQL
> Server Properties" knowing that I checked the
> "Use SQL Server authentication".
> Am I going to use that account in the Control Panel - Services ?
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> >
> > Probably Full-Text Search is running as System Local and has no
> permissions
> > to SQL Server. Try running the Full-Text Search using the same service
> > account as SQL Server. This time use the Control Panel / Services applet.
> The
> > service must be named MSSearch or something similar.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > I created a catalog in SQL Server 2000 but cannot get results from this
> > > catalog by issuing the SQL with CONTAINS. It just return empty set with
> > > headers only.
> > > The status of the catalog is always idle even after full population.
> > >
> > >
> > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > >
> > > > Full-Text Search is another Windows service. What is the problem?
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > Hi Ben,
> > > > >
> > > > > I am asking this question because I still stuck in my full-text
> search
> > > > > problem.
> > > > > I tried to do many thing to get around it like to change user
> account to
> > > > > start up SQLServer.
> > > > > I just posted a thread regarding changing the account start up the
> > > > > SQLServer, if you can help me that would be great.
> > > > >
> > > > >
> > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > >
> > > > > > On Enterprise Manager, select your server, right-click, Properties
> and
> > > > > > Security tab.
> > > > > >
> > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > Database Administrator
> > > > > >
> > > > > >
> > > > > > "Alan" wrote:
> > > > > >
> > > > > > > How do I know which account under which the SQL Server 2000 runs
> ?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hi Ben,
A little progress, at least, in EM, the catalog is in recovering status.
What I did was:
1) Goto edit SQL Server registration info
2) My account in SQL Server is 'alan', this is just the login for SQL
Server, so I changed to Windows Authentication
3) Then goto Control Panel - Services - Microsoft Search
4) Double click
5) LogOn tab
6) I change to use my Windows machine login, ie 'Alan Tse'.
This 'Alan Tse' is the login that I use to login to my pc everyday when
I start up my pc.
7) Click OK
8) Then I checked the Control Panel - Services - Microsoft Search, the user
is .\Alan Tse
Are SQL Server and Microsoft Search using the same login, .\Alan Tse?
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:OA0$IAHhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> Hi Ben,
> I go to EM, right click my server instance, select the "Edit Registered
SQL
> Server Properties" knowing that I checked the
> "Use SQL Server authentication".
> Am I going to use that account in the Control Panel - Services ?
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> >
> > Probably Full-Text Search is running as System Local and has no
> permissions
> > to SQL Server. Try running the Full-Text Search using the same service
> > account as SQL Server. This time use the Control Panel / Services
applet.
> The
> > service must be named MSSearch or something similar.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > I created a catalog in SQL Server 2000 but cannot get results from
this
> > > catalog by issuing the SQL with CONTAINS. It just return empty set
with
> > > headers only.
> > > The status of the catalog is always idle even after full population.
> > >
> > >
> > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > >
> > > > Full-Text Search is another Windows service. What is the problem?
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > Hi Ben,
> > > > >
> > > > > I am asking this question because I still stuck in my full-text
> search
> > > > > problem.
> > > > > I tried to do many thing to get around it like to change user
> account to
> > > > > start up SQLServer.
> > > > > I just posted a thread regarding changing the account start up the
> > > > > SQLServer, if you can help me that would be great.
> > > > >
> > > > >
> > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > >
> > > > > > On Enterprise Manager, select your server, right-click,
Properties
> and
> > > > > > Security tab.
> > > > > >
> > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > Database Administrator
> > > > > >
> > > > > >
> > > > > > "Alan" wrote:
> > > > > >
> > > > > > > How do I know which account under which the SQL Server 2000
runs
> ?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>|||Hi Ben,
What account should I use on the Security Tab? Use Windows login or SQL
Server login ?
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:FCFB7F64-56BD-4C94-A87B-AC0894AA585B@.microsoft.com...
> Right-click the server and select 'Properties', follow the instructions I
> already sent.
> Do not select 'Edit SQL Server Registration Properties'.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Hi Ben,
> >
> > I go to EM, right click my server instance, select the "Edit Registered
SQL
> > Server Properties" knowing that I checked the
> > "Use SQL Server authentication".
> > Am I going to use that account in the Control Panel - Services ?
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> > >
> > > Probably Full-Text Search is running as System Local and has no
> > permissions
> > > to SQL Server. Try running the Full-Text Search using the same service
> > > account as SQL Server. This time use the Control Panel / Services
applet.
> > The
> > > service must be named MSSearch or something similar.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > I created a catalog in SQL Server 2000 but cannot get results from
this
> > > > catalog by issuing the SQL with CONTAINS. It just return empty set
with
> > > > headers only.
> > > > The status of the catalog is always idle even after full population.
> > > >
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > > >
> > > > > Full-Text Search is another Windows service. What is the problem?
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "Alan" wrote:
> > > > >
> > > > > > Hi Ben,
> > > > > >
> > > > > > I am asking this question because I still stuck in my full-text
> > search
> > > > > > problem.
> > > > > > I tried to do many thing to get around it like to change user
> > account to
> > > > > > start up SQLServer.
> > > > > > I just posted a thread regarding changing the account start up
the
> > > > > > SQLServer, if you can help me that would be great.
> > > > > >
> > > > > >
> > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > > >
> > > > > > > On Enterprise Manager, select your server, right-click,
Properties
> > and
> > > > > > > Security tab.
> > > > > > >
> > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > Database Administrator
> > > > > > >
> > > > > > >
> > > > > > > "Alan" wrote:
> > > > > > >
> > > > > > > > How do I know which account under which the SQL Server 2000
runs
> > ?
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Alan,
Read my previous messages carefully. 'Edit SQL Server Registration
Properties' do not change the service account but the account you are using
to connect.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> Hi Ben,
> A little progress, at least, in EM, the catalog is in recovering status.
> What I did was:
> 1) Goto edit SQL Server registration info
> 2) My account in SQL Server is 'alan', this is just the login for SQL
> Server, so I changed to Windows Authentication
> 3) Then goto Control Panel - Services - Microsoft Search
> 4) Double click
> 5) LogOn tab
> 6) I change to use my Windows machine login, ie 'Alan Tse'.
> This 'Alan Tse' is the login that I use to login to my pc everyday when
> I start up my pc.
> 7) Click OK
> 8) Then I checked the Control Panel - Services - Microsoft Search, the user
> is .\Alan Tse
> Are SQL Server and Microsoft Search using the same login, .\Alan Tse?
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:OA0$IAHhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> > Hi Ben,
> >
> > I go to EM, right click my server instance, select the "Edit Registered
> SQL
> > Server Properties" knowing that I checked the
> > "Use SQL Server authentication".
> > Am I going to use that account in the Control Panel - Services ?
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> > >
> > > Probably Full-Text Search is running as System Local and has no
> > permissions
> > > to SQL Server. Try running the Full-Text Search using the same service
> > > account as SQL Server. This time use the Control Panel / Services
> applet.
> > The
> > > service must be named MSSearch or something similar.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > I created a catalog in SQL Server 2000 but cannot get results from
> this
> > > > catalog by issuing the SQL with CONTAINS. It just return empty set
> with
> > > > headers only.
> > > > The status of the catalog is always idle even after full population.
> > > >
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > > >
> > > > > Full-Text Search is another Windows service. What is the problem?
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "Alan" wrote:
> > > > >
> > > > > > Hi Ben,
> > > > > >
> > > > > > I am asking this question because I still stuck in my full-text
> > search
> > > > > > problem.
> > > > > > I tried to do many thing to get around it like to change user
> > account to
> > > > > > start up SQLServer.
> > > > > > I just posted a thread regarding changing the account start up the
> > > > > > SQLServer, if you can help me that would be great.
> > > > > >
> > > > > >
> > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > > >
> > > > > > > On Enterprise Manager, select your server, right-click,
> Properties
> > and
> > > > > > > Security tab.
> > > > > > >
> > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > Database Administrator
> > > > > > >
> > > > > > >
> > > > > > > "Alan" wrote:
> > > > > > >
> > > > > > > > How do I know which account under which the SQL Server 2000
> runs
> > ?
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
>
>|||Hi Ben,
My machine name is 'ATSE', windows login is 'Alan Tse'.
What I did again:
1) Goto EM, right click the server instance, select properties
2) On Security tab
3) Check Windows authentication
4) On Startup service account groupbox, select the 'This account", fill in
ATSE\Alan Tse with password.
5) An error comes up saying Logon failure: unkown user name or bad password.
I am not understand why I got this error, I double checked in the EM that I
actully have this login. This is the domain\user. This 'ATSE\Alan Tse' is
the login I use everytime I start up my pc.
I think you gave me guidances very near the solution.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:B8082948-C346-4110-9A36-5340F21D6A38@.microsoft.com...
> Alan,
> Read my previous messages carefully. 'Edit SQL Server Registration
> Properties' do not change the service account but the account you are
using
> to connect.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Hi Ben,
> >
> > A little progress, at least, in EM, the catalog is in recovering status.
> > What I did was:
> > 1) Goto edit SQL Server registration info
> > 2) My account in SQL Server is 'alan', this is just the login for SQL
> > Server, so I changed to Windows Authentication
> > 3) Then goto Control Panel - Services - Microsoft Search
> > 4) Double click
> > 5) LogOn tab
> > 6) I change to use my Windows machine login, ie 'Alan Tse'.
> > This 'Alan Tse' is the login that I use to login to my pc everyday
when
> > I start up my pc.
> > 7) Click OK
> > 8) Then I checked the Control Panel - Services - Microsoft Search, the
user
> > is .\Alan Tse
> >
> > Are SQL Server and Microsoft Search using the same login, .\Alan Tse?
> >
> > "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> > news:OA0$IAHhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> > > Hi Ben,
> > >
> > > I go to EM, right click my server instance, select the "Edit
Registered
> > SQL
> > > Server Properties" knowing that I checked the
> > > "Use SQL Server authentication".
> > > Am I going to use that account in the Control Panel - Services ?
> > >
> > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> > > >
> > > > Probably Full-Text Search is running as System Local and has no
> > > permissions
> > > > to SQL Server. Try running the Full-Text Search using the same
service
> > > > account as SQL Server. This time use the Control Panel / Services
> > applet.
> > > The
> > > > service must be named MSSearch or something similar.
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > I created a catalog in SQL Server 2000 but cannot get results
from
> > this
> > > > > catalog by issuing the SQL with CONTAINS. It just return empty set
> > with
> > > > > headers only.
> > > > > The status of the catalog is always idle even after full
population.
> > > > >
> > > > >
> > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > > > >
> > > > > > Full-Text Search is another Windows service. What is the
problem?
> > > > > >
> > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > Database Administrator
> > > > > >
> > > > > >
> > > > > > "Alan" wrote:
> > > > > >
> > > > > > > Hi Ben,
> > > > > > >
> > > > > > > I am asking this question because I still stuck in my
full-text
> > > search
> > > > > > > problem.
> > > > > > > I tried to do many thing to get around it like to change user
> > > account to
> > > > > > > start up SQLServer.
> > > > > > > I just posted a thread regarding changing the account start up
the
> > > > > > > SQLServer, if you can help me that would be great.
> > > > > > >
> > > > > > >
> > > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > > > >
> > > > > > > > On Enterprise Manager, select your server, right-click,
> > Properties
> > > and
> > > > > > > > Security tab.
> > > > > > > >
> > > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > > Database Administrator
> > > > > > > >
> > > > > > > >
> > > > > > > > "Alan" wrote:
> > > > > > > >
> > > > > > > > > How do I know which account under which the SQL Server
2000
> > runs
> > > ?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >|||If this is a local account just type .\Alan Tse
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> Hi Ben,
> My machine name is 'ATSE', windows login is 'Alan Tse'.
> What I did again:
> 1) Goto EM, right click the server instance, select properties
> 2) On Security tab
> 3) Check Windows authentication
> 4) On Startup service account groupbox, select the 'This account", fill in
> ATSE\Alan Tse with password.
> 5) An error comes up saying Logon failure: unkown user name or bad password.
> I am not understand why I got this error, I double checked in the EM that I
> actully have this login. This is the domain\user. This 'ATSE\Alan Tse' is
> the login I use everytime I start up my pc.
> I think you gave me guidances very near the solution.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:B8082948-C346-4110-9A36-5340F21D6A38@.microsoft.com...
> >
> > Alan,
> >
> > Read my previous messages carefully. 'Edit SQL Server Registration
> > Properties' do not change the service account but the account you are
> using
> > to connect.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > Hi Ben,
> > >
> > > A little progress, at least, in EM, the catalog is in recovering status.
> > > What I did was:
> > > 1) Goto edit SQL Server registration info
> > > 2) My account in SQL Server is 'alan', this is just the login for SQL
> > > Server, so I changed to Windows Authentication
> > > 3) Then goto Control Panel - Services - Microsoft Search
> > > 4) Double click
> > > 5) LogOn tab
> > > 6) I change to use my Windows machine login, ie 'Alan Tse'.
> > > This 'Alan Tse' is the login that I use to login to my pc everyday
> when
> > > I start up my pc.
> > > 7) Click OK
> > > 8) Then I checked the Control Panel - Services - Microsoft Search, the
> user
> > > is .\Alan Tse
> > >
> > > Are SQL Server and Microsoft Search using the same login, .\Alan Tse?
> > >
> > > "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> > > news:OA0$IAHhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> > > > Hi Ben,
> > > >
> > > > I go to EM, right click my server instance, select the "Edit
> Registered
> > > SQL
> > > > Server Properties" knowing that I checked the
> > > > "Use SQL Server authentication".
> > > > Am I going to use that account in the Control Panel - Services ?
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> > > > >
> > > > > Probably Full-Text Search is running as System Local and has no
> > > > permissions
> > > > > to SQL Server. Try running the Full-Text Search using the same
> service
> > > > > account as SQL Server. This time use the Control Panel / Services
> > > applet.
> > > > The
> > > > > service must be named MSSearch or something similar.
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "Alan" wrote:
> > > > >
> > > > > > I created a catalog in SQL Server 2000 but cannot get results
> from
> > > this
> > > > > > catalog by issuing the SQL with CONTAINS. It just return empty set
> > > with
> > > > > > headers only.
> > > > > > The status of the catalog is always idle even after full
> population.
> > > > > >
> > > > > >
> > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > > > > >
> > > > > > > Full-Text Search is another Windows service. What is the
> problem?
> > > > > > >
> > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > Database Administrator
> > > > > > >
> > > > > > >
> > > > > > > "Alan" wrote:
> > > > > > >
> > > > > > > > Hi Ben,
> > > > > > > >
> > > > > > > > I am asking this question because I still stuck in my
> full-text
> > > > search
> > > > > > > > problem.
> > > > > > > > I tried to do many thing to get around it like to change user
> > > > account to
> > > > > > > > start up SQLServer.
> > > > > > > > I just posted a thread regarding changing the account start up
> the
> > > > > > > > SQLServer, if you can help me that would be great.
> > > > > > > >
> > > > > > > >
> > > > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > > > > >
> > > > > > > > > On Enterprise Manager, select your server, right-click,
> > > Properties
> > > > and
> > > > > > > > > Security tab.
> > > > > > > > >
> > > > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > > > Database Administrator
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Alan" wrote:
> > > > > > > > >
> > > > > > > > > > How do I know which account under which the SQL Server
> 2000
> > > runs
> > > > ?
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||Alan,
I need to leave. You should have all the instructions on these messages.
Good luck.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> If this is a local account just type .\Alan Tse
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Hi Ben,
> >
> > My machine name is 'ATSE', windows login is 'Alan Tse'.
> > What I did again:
> > 1) Goto EM, right click the server instance, select properties
> > 2) On Security tab
> > 3) Check Windows authentication
> > 4) On Startup service account groupbox, select the 'This account", fill in
> > ATSE\Alan Tse with password.
> > 5) An error comes up saying Logon failure: unkown user name or bad password.
> >
> > I am not understand why I got this error, I double checked in the EM that I
> > actully have this login. This is the domain\user. This 'ATSE\Alan Tse' is
> > the login I use everytime I start up my pc.
> >
> > I think you gave me guidances very near the solution.
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:B8082948-C346-4110-9A36-5340F21D6A38@.microsoft.com...
> > >
> > > Alan,
> > >
> > > Read my previous messages carefully. 'Edit SQL Server Registration
> > > Properties' do not change the service account but the account you are
> > using
> > > to connect.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > Hi Ben,
> > > >
> > > > A little progress, at least, in EM, the catalog is in recovering status.
> > > > What I did was:
> > > > 1) Goto edit SQL Server registration info
> > > > 2) My account in SQL Server is 'alan', this is just the login for SQL
> > > > Server, so I changed to Windows Authentication
> > > > 3) Then goto Control Panel - Services - Microsoft Search
> > > > 4) Double click
> > > > 5) LogOn tab
> > > > 6) I change to use my Windows machine login, ie 'Alan Tse'.
> > > > This 'Alan Tse' is the login that I use to login to my pc everyday
> > when
> > > > I start up my pc.
> > > > 7) Click OK
> > > > 8) Then I checked the Control Panel - Services - Microsoft Search, the
> > user
> > > > is .\Alan Tse
> > > >
> > > > Are SQL Server and Microsoft Search using the same login, .\Alan Tse?
> > > >
> > > > "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> > > > news:OA0$IAHhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> > > > > Hi Ben,
> > > > >
> > > > > I go to EM, right click my server instance, select the "Edit
> > Registered
> > > > SQL
> > > > > Server Properties" knowing that I checked the
> > > > > "Use SQL Server authentication".
> > > > > Am I going to use that account in the Control Panel - Services ?
> > > > >
> > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> > > > > >
> > > > > > Probably Full-Text Search is running as System Local and has no
> > > > > permissions
> > > > > > to SQL Server. Try running the Full-Text Search using the same
> > service
> > > > > > account as SQL Server. This time use the Control Panel / Services
> > > > applet.
> > > > > The
> > > > > > service must be named MSSearch or something similar.
> > > > > >
> > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > Database Administrator
> > > > > >
> > > > > >
> > > > > > "Alan" wrote:
> > > > > >
> > > > > > > I created a catalog in SQL Server 2000 but cannot get results
> > from
> > > > this
> > > > > > > catalog by issuing the SQL with CONTAINS. It just return empty set
> > > > with
> > > > > > > headers only.
> > > > > > > The status of the catalog is always idle even after full
> > population.
> > > > > > >
> > > > > > >
> > > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > > > > > >
> > > > > > > > Full-Text Search is another Windows service. What is the
> > problem?
> > > > > > > >
> > > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > > Database Administrator
> > > > > > > >
> > > > > > > >
> > > > > > > > "Alan" wrote:
> > > > > > > >
> > > > > > > > > Hi Ben,
> > > > > > > > >
> > > > > > > > > I am asking this question because I still stuck in my
> > full-text
> > > > > search
> > > > > > > > > problem.
> > > > > > > > > I tried to do many thing to get around it like to change user
> > > > > account to
> > > > > > > > > start up SQLServer.
> > > > > > > > > I just posted a thread regarding changing the account start up
> > the
> > > > > > > > > SQLServer, if you can help me that would be great.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > > > > news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > > > > > >
> > > > > > > > > > On Enterprise Manager, select your server, right-click,
> > > > Properties
> > > > > and
> > > > > > > > > > Security tab.
> > > > > > > > > >
> > > > > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > > > > Database Administrator
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Alan" wrote:
> > > > > > > > > >
> > > > > > > > > > > How do I know which account under which the SQL Server
> > 2000
> > > > runs
> > > > > ?
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Ben,
Now I can use my local account, ATSE\Alan Tse as 'Log On As' in the MSSQL
Server service and SQLAgent service.
Then execute the T-SQL to add to a sysadmin role
exec sp_addsrvrolemember N'ATSE\Alan Tse', sysadmin
and
select * from master.dbo.syslogins where loginname = N'ATSE\Alan Tse'
showing sysadmin is 1.
But the catalog still idle and not populated.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:69236D8A-C417-4D82-8B51-FB9E3FCCDEAA@.microsoft.com...
> Alan,
> I need to leave. You should have all the instructions on these messages.
> Good luck.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Ben Nevarez" wrote:
> >
> > If this is a local account just type .\Alan Tse
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > Hi Ben,
> > >
> > > My machine name is 'ATSE', windows login is 'Alan Tse'.
> > > What I did again:
> > > 1) Goto EM, right click the server instance, select properties
> > > 2) On Security tab
> > > 3) Check Windows authentication
> > > 4) On Startup service account groupbox, select the 'This account",
fill in
> > > ATSE\Alan Tse with password.
> > > 5) An error comes up saying Logon failure: unkown user name or bad
password.
> > >
> > > I am not understand why I got this error, I double checked in the EM
that I
> > > actully have this login. This is the domain\user. This 'ATSE\Alan Tse'
is
> > > the login I use everytime I start up my pc.
> > >
> > > I think you gave me guidances very near the solution.
> > >
> > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > news:B8082948-C346-4110-9A36-5340F21D6A38@.microsoft.com...
> > > >
> > > > Alan,
> > > >
> > > > Read my previous messages carefully. 'Edit SQL Server Registration
> > > > Properties' do not change the service account but the account you
are
> > > using
> > > > to connect.
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > Hi Ben,
> > > > >
> > > > > A little progress, at least, in EM, the catalog is in recovering
status.
> > > > > What I did was:
> > > > > 1) Goto edit SQL Server registration info
> > > > > 2) My account in SQL Server is 'alan', this is just the login for
SQL
> > > > > Server, so I changed to Windows Authentication
> > > > > 3) Then goto Control Panel - Services - Microsoft Search
> > > > > 4) Double click
> > > > > 5) LogOn tab
> > > > > 6) I change to use my Windows machine login, ie 'Alan Tse'.
> > > > > This 'Alan Tse' is the login that I use to login to my pc
everyday
> > > when
> > > > > I start up my pc.
> > > > > 7) Click OK
> > > > > 8) Then I checked the Control Panel - Services - Microsoft Search,
the
> > > user
> > > > > is .\Alan Tse
> > > > >
> > > > > Are SQL Server and Microsoft Search using the same login, .\Alan
Tse?
> > > > >
> > > > > "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> > > > > news:OA0$IAHhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> > > > > > Hi Ben,
> > > > > >
> > > > > > I go to EM, right click my server instance, select the "Edit
> > > Registered
> > > > > SQL
> > > > > > Server Properties" knowing that I checked the
> > > > > > "Use SQL Server authentication".
> > > > > > Am I going to use that account in the Control Panel - Services ?
> > > > > >
> > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > news:35A07094-2665-4F64-B5F8-841D15588416@.microsoft.com...
> > > > > > >
> > > > > > > Probably Full-Text Search is running as System Local and has
no
> > > > > > permissions
> > > > > > > to SQL Server. Try running the Full-Text Search using the same
> > > service
> > > > > > > account as SQL Server. This time use the Control Panel /
Services
> > > > > applet.
> > > > > > The
> > > > > > > service must be named MSSearch or something similar.
> > > > > > >
> > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > Database Administrator
> > > > > > >
> > > > > > >
> > > > > > > "Alan" wrote:
> > > > > > >
> > > > > > > > I created a catalog in SQL Server 2000 but cannot get
results
> > > from
> > > > > this
> > > > > > > > catalog by issuing the SQL with CONTAINS. It just return
empty set
> > > > > with
> > > > > > > > headers only.
> > > > > > > > The status of the catalog is always idle even after full
> > > population.
> > > > > > > >
> > > > > > > >
> > > > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > > > news:39EFD002-9F27-4F56-9658-3BEBC71973AD@.microsoft.com...
> > > > > > > > >
> > > > > > > > > Full-Text Search is another Windows service. What is the
> > > problem?
> > > > > > > > >
> > > > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > > > Database Administrator
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Alan" wrote:
> > > > > > > > >
> > > > > > > > > > Hi Ben,
> > > > > > > > > >
> > > > > > > > > > I am asking this question because I still stuck in my
> > > full-text
> > > > > > search
> > > > > > > > > > problem.
> > > > > > > > > > I tried to do many thing to get around it like to change
user
> > > > > > account to
> > > > > > > > > > start up SQLServer.
> > > > > > > > > > I just posted a thread regarding changing the account
start up
> > > the
> > > > > > > > > > SQLServer, if you can help me that would be great.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > > > > > > >
news:67396273-81A5-4066-A066-8459CFB4EE3D@.microsoft.com...
> > > > > > > > > > >
> > > > > > > > > > > On Enterprise Manager, select your server,
right-click,
> > > > > Properties
> > > > > > and
> > > > > > > > > > > Security tab.
> > > > > > > > > > >
> > > > > > > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > > > > > > Database Administrator
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "Alan" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > How do I know which account under which the SQL
Server
> > > 2000
> > > > > runs
> > > > > > ?
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >|||Alan wrote:
> Hi Ben,
> Now I can use my local account, ATSE\Alan Tse as 'Log On As' in the MSSQL
> Server service and SQLAgent service.
> Then execute the T-SQL to add to a sysadmin role
> exec sp_addsrvrolemember N'ATSE\Alan Tse', sysadmin
> and
> select * from master.dbo.syslogins where loginname = N'ATSE\Alan Tse'
> showing sysadmin is 1.
> But the catalog still idle and not populated.
>
Hi Alan
Could this article has something to do with your problem :
http://support.microsoft.com/kb/317746/
It's mentioned in the link I send you in another thread regarding your
account change problems.
--
Regards
Steen Schlüter Persson
DBA|||Hi Steen,
I found the NT Authority\System is already in SQL Server "Security ->
Logins".
I executed the T-SQL from the article and repopulated the Catalog again, the
status is still idle after refresh. The item count is 0.
By the way, where is the event log stored so that I can look at that?
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OMIPHiUhGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Alan wrote:
> > Hi Ben,
> > Now I can use my local account, ATSE\Alan Tse as 'Log On As' in the
MSSQL
> > Server service and SQLAgent service.
> > Then execute the T-SQL to add to a sysadmin role
> > exec sp_addsrvrolemember N'ATSE\Alan Tse', sysadmin
> > and
> > select * from master.dbo.syslogins where loginname = N'ATSE\Alan Tse'
> > showing sysadmin is 1.
> >
> > But the catalog still idle and not populated.
> >
> Hi Alan
> Could this article has something to do with your problem :
> http://support.microsoft.com/kb/317746/
> It's mentioned in the link I send you in another thread regarding your
> account change problems.
> --
> Regards
> Steen Schlüter Persson
> DBA

Account to Run SQL under?

Currently, our network/server/domain admins have admin control of our sql
servers. One manager wants to run sql under a special local admin account
to restrict this access, but then we will be effected by the password change
requirement, which could break sql every 90 days.
What is the best way to run sql so that the server admins don't have sql
admin access? Can I run it under "local system", and restrict machine
admins?Its not a wise choice to make the User IDs local to the
server, the reason is that non local user id's (i.e.
domain accounts) can be allocated email accounts so sql
server can send emails, whereas local userid's cannot.
If you do not want local administrators to have system
admin access rights then all you need to so is restrict
the BUILTIN\Administrators rights.
Peter
"Only two things are infinite, the universe and human
stupidity, and I'm not sure about the former."
Albert Einstein
>--Original Message--
>Currently, our network/server/domain admins have admin
control of our sql
>servers. One manager wants to run sql under a special
local admin account
>to restrict this access, but then we will be effected by
the password change
>requirement, which could break sql every 90 days.
>What is the best way to run sql so that the server admins
don't have sql
>admin access? Can I run it under "local system", and
restrict machine
>admins?
>
>.
>|||First of all, it is imposible to stop God from having access. And, in the
domain, Domain Administrators are God. However, you can slow them down
quite a bit. Here is a KB that details the procedures. Also know that if
you use AD Global Groups to grant the SQL Server DBAs administrative access,
which is a Best Practice, all the Domain Admin has to do is add themself or
the Domain Admins group to yours.
How to impede Windows NT administrators from administering a clustered
instance of SQL Server
http://support.microsoft.com/kb/263712/EN-US/
How to change the SQL Server or SQL Server Agent Service account without
using SQL Enterprise Manager in SQL Server 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;283811
If you suspect foul play, it would be better to run some sort of Audit
Logging on your system and then once captured, bring it the attention of the
Data Center Director's attention. Keep in mind that just like the DBAs,
with great power comes great responsibility. No one should be placed in a
role that they are not qualified, and responsible for, to perform.
Just like the DBAs have the authority to delete every user/system database,
the responsibility is to preserve that data. It is no different for the
Domain Admins. They have the authority to disrupt operations but the
responsibility to preserve them. If you find an individual, or team, that
does not play by these rules, that must be made know to the Operations
Manager, regardless of the title or the position of the offender.
Sincerely,
Anthony Thomas
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:05b901c51b4e$87a454e0$a501280a@.phx.gbl...
Its not a wise choice to make the User IDs local to the
server, the reason is that non local user id's (i.e.
domain accounts) can be allocated email accounts so sql
server can send emails, whereas local userid's cannot.
If you do not want local administrators to have system
admin access rights then all you need to so is restrict
the BUILTIN\Administrators rights.
Peter
"Only two things are infinite, the universe and human
stupidity, and I'm not sure about the former."
Albert Einstein
>--Original Message--
>Currently, our network/server/domain admins have admin
control of our sql
>servers. One manager wants to run sql under a special
local admin account
>to restrict this access, but then we will be effected by
the password change
>requirement, which could break sql every 90 days.
>What is the best way to run sql so that the server admins
don't have sql
>admin access? Can I run it under "local system", and
restrict machine
>admins?
>
>.
>

Account to Run SQL under?

Its not a wise choice to make the User IDs local to the
server, the reason is that non local user id's (i.e.
domain accounts) can be allocated email accounts so sql
server can send emails, whereas local userid's cannot.
If you do not want local administrators to have system
admin access rights then all you need to so is restrict
the BUILTIN\Administrators rights.
Peter
"Only two things are infinite, the universe and human
stupidity, and I'm not sure about the former."
Albert Einstein

>--Original Message--
>Currently, our network/server/domain admins have admin
control of our sql
>servers. One manager wants to run sql under a special
local admin account
>to restrict this access, but then we will be effected by
the password change
>requirement, which could break sql every 90 days.
>What is the best way to run sql so that the server admins
don't have sql
>admin access? Can I run it under "local system", and
restrict machine
>admins?
>
>.
>
First of all, it is imposible to stop God from having access. And, in the
domain, Domain Administrators are God. However, you can slow them down
quite a bit. Here is a KB that details the procedures. Also know that if
you use AD Global Groups to grant the SQL Server DBAs administrative access,
which is a Best Practice, all the Domain Admin has to do is add themself or
the Domain Admins group to yours.
How to impede Windows NT administrators from administering a clustered
instance of SQL Server
http://support.microsoft.com/kb/263712/EN-US/
How to change the SQL Server or SQL Server Agent Service account without
using SQL Enterprise Manager in SQL Server 2000
http://support.microsoft.com/default...b;en-us;283811
If you suspect foul play, it would be better to run some sort of Audit
Logging on your system and then once captured, bring it the attention of the
Data Center Director's attention. Keep in mind that just like the DBAs,
with great power comes great responsibility. No one should be placed in a
role that they are not qualified, and responsible for, to perform.
Just like the DBAs have the authority to delete every user/system database,
the responsibility is to preserve that data. It is no different for the
Domain Admins. They have the authority to disrupt operations but the
responsibility to preserve them. If you find an individual, or team, that
does not play by these rules, that must be made know to the Operations
Manager, regardless of the title or the position of the offender.
Sincerely,
Anthony Thomas

"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:05b901c51b4e$87a454e0$a501280a@.phx.gbl...
Its not a wise choice to make the User IDs local to the
server, the reason is that non local user id's (i.e.
domain accounts) can be allocated email accounts so sql
server can send emails, whereas local userid's cannot.
If you do not want local administrators to have system
admin access rights then all you need to so is restrict
the BUILTIN\Administrators rights.
Peter
"Only two things are infinite, the universe and human
stupidity, and I'm not sure about the former."
Albert Einstein

>--Original Message--
>Currently, our network/server/domain admins have admin
control of our sql
>servers. One manager wants to run sql under a special
local admin account
>to restrict this access, but then we will be effected by
the password change
>requirement, which could break sql every 90 days.
>What is the best way to run sql so that the server admins
don't have sql
>admin access? Can I run it under "local system", and
restrict machine
>admins?
>
>.
>

Account to Run SQL under?

Its not a wise choice to make the User IDs local to the
server, the reason is that non local user id's (i.e.
domain accounts) can be allocated email accounts so sql
server can send emails, whereas local userid's cannot.
If you do not want local administrators to have system
admin access rights then all you need to so is restrict
the BUILTIN\Administrators rights.
Peter
"Only two things are infinite, the universe and human
stupidity, and I'm not sure about the former."
Albert Einstein

>--Original Message--
>Currently, our network/server/domain admins have admin
control of our sql
>servers. One manager wants to run sql under a special
local admin account
>to restrict this access, but then we will be effected by
the password change
>requirement, which could break sql every 90 days.
>What is the best way to run sql so that the server admins
don't have sql
>admin access? Can I run it under "local system", and
restrict machine
>admins?
>
>.
>First of all, it is imposible to stop God from having access. And, in the
domain, Domain Administrators are God. However, you can slow them down
quite a bit. Here is a KB that details the procedures. Also know that if
you use AD Global Groups to grant the SQL Server DBAs administrative access,
which is a Best Practice, all the Domain Admin has to do is add themself or
the Domain Admins group to yours.
How to impede Windows NT administrators from administering a clustered
instance of SQL Server
http://support.microsoft.com/kb/263712/EN-US/
How to change the SQL Server or SQL Server Agent Service account without
using SQL Enterprise Manager in SQL Server 2000
http://support.microsoft.com/defaul...kb;en-us;283811
If you suspect foul play, it would be better to run some sort of Audit
Logging on your system and then once captured, bring it the attention of the
Data Center Director's attention. Keep in mind that just like the DBAs,
with great power comes great responsibility. No one should be placed in a
role that they are not qualified, and responsible for, to perform.
Just like the DBAs have the authority to delete every user/system database,
the responsibility is to preserve that data. It is no different for the
Domain Admins. They have the authority to disrupt operations but the
responsibility to preserve them. If you find an individual, or team, that
does not play by these rules, that must be made know to the Operations
Manager, regardless of the title or the position of the offender.
Sincerely,
Anthony Thomas
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:05b901c51b4e$87a454e0$a501280a@.phx.gbl...
Its not a wise choice to make the User IDs local to the
server, the reason is that non local user id's (i.e.
domain accounts) can be allocated email accounts so sql
server can send emails, whereas local userid's cannot.
If you do not want local administrators to have system
admin access rights then all you need to so is restrict
the BUILTIN\Administrators rights.
Peter
"Only two things are infinite, the universe and human
stupidity, and I'm not sure about the former."
Albert Einstein

>--Original Message--
>Currently, our network/server/domain admins have admin
control of our sql
>servers. One manager wants to run sql under a special
local admin account
>to restrict this access, but then we will be effected by
the password change
>requirement, which could break sql every 90 days.
>What is the best way to run sql so that the server admins
don't have sql
>admin access? Can I run it under "local system", and
restrict machine
>admins?
>
>.
>

Account to access SQL

What is the best way of accessing a sql server on a live server? shall I use Integrated windows or use a special user account? If I use a user account, what are the needed priviledges to give it?

thank you

Well, the real answers to those questions are: "It depends." Froma practical standpoint, choosing Windows authentication vs. SQLauthentication is going to come down to what your needs are and what'sgoing to provide the smallest attack surface whilst meeting thoseneeds.
The needed privileges are also going to vary from application toapplication. Personally, I shoot for not allowing anything butexecute permissions on the stored procedures that the app needs tofunction, and nothing else. That gives me a stong, well-definedAPI for the database that allows me to control (to an extent) how thedatabase is used. Again, YMMV depending on the needs of yourapplication.

Account problem when creating a new publication

I've got SQL Server Developer installed on my laptop. I'm trying to get
merge replication to work, but when I try to create a new publisher, when I
select the option to make SQL Server on my laptop its own distributor, I
then get an error msg. saying I've chosen a local system account, and
replication will not work. It then sends me to a publications properties
form to select a new account. I have no other accounts, this is just run
from my laptop. Can some kind soul help me and tell me what I need to do
to get this working?
My end result is to be able to get merge replication set up so I can sync.
with a handheld device using SQL Server 2000 CE.
Thanks in advance for any assistance.
The account that SQL Server and the Agent need to run in something other
than a Local account.
"DaveM" <nosebop@.yahoo.com> wrote in message
news:OFNJHikLFHA.2824@.TK2MSFTNGP10.phx.gbl...
> I've got SQL Server Developer installed on my laptop. I'm trying to get
> merge replication to work, but when I try to create a new publisher, when
I
> select the option to make SQL Server on my laptop its own distributor, I
> then get an error msg. saying I've chosen a local system account, and
> replication will not work. It then sends me to a publications properties
> form to select a new account. I have no other accounts, this is just
run
> from my laptop. Can some kind soul help me and tell me what I need to do
> to get this working?
> My end result is to be able to get merge replication set up so I can sync.
> with a handheld device using SQL Server 2000 CE.
> Thanks in advance for any assistance.
>
>

Account lockout problem with sp_send_dbmail

I am running this code in a job:

EXEC msdb.[dbo].sp_send_dbmail
@.profile_name = 'Blah Blah',
@.recipients = 'blah@.blah.com',
@.subject = 'Server Report 1',
@.body = '',
@.execute_query_database = 'DBInfo',
@.query = 'EXEC dbo.usp_SvrRpt1;',
@.query_result_header = 0,
@.query_result_width = 300,
--@.attach_query_result_as_file = 1,
@.query_result_separator = '';
GO

Because I am creating a total of 6 server reports, I alter the subject and query like so:

@.subject = 'Server Report X' --where X is replaced with values 1 to 6
@.query = 'EXEC dbo.usp_SvrRptX' --where X is replaced with values 1 to 6

I can run 4 reports without any problems. If I run more than 4 reports in the same batch, my network account under Windows 2000 Activity Directory is locked out as if I had erroneously mistyped my password the number of time to meet the lockout requirement - in this case, the number is 3 login tries.

Is this a known problem with Database Mail? If so, is there a fix or workaround?

Thanks,

Neal

I am experiencing the same problem. The database mail profile uses basic authentication with a domain account (that is being locked out when the SP sends more than 3 messages). Try this script with @.i values from 3 upwards and see...

declare @.i int, @.status int, @.c varchar(128)
set @.i = 3

while @.i>0 begin
set @.c = 'Mensaje de prueba ' + CONVERT(varchar, @.i)
EXEC @.status = msdb.dbo.sp_send_dbmail
@.recipients = 'yourown@.email.here.com',
@.body = @.c,
@.subject = @.c
set @.i = @.i - 1
end

Did you manage to solve this out?

|||Have you checked the Active Directory policies in this case, may be a restriction to not to allow this account on more 5 instances on network. Might take help of sysadmin to see whether it is restricted on AD side.|||

Satya,

Turns out that was the problem. Thanks for getting back to me on this.

Neal

|||

Nealgseattle, Satya:

I am trying to find where that group policy dealing with network instances is defined without any luck. I started looking beneath Users configuration branch in Default Domain Policy's but when I couldn't find it I jumped to browse computer configuration branch also. Since our OSes are spanish we have some of the policies described in english and others translated. Knowing the path and full name of the policy would help a lot in checking if our problem is because of the same reason.

Regards.

Account lockout problem with sp_send_dbmail

I am running this code in a job:

Because I am creating a total of 6 server reports, I alter the subject and query like so:

@.subject = 'Server Report X' --where X is replaced with values 1 to 6
@.query = 'EXEC dbo.usp_SvrRptX' --where X is replaced with values 1 to 6

Is this a known problem with Database Mail? If so, is there a fix or workaround?

Thanks,

Neal

declare @.i int, @.status int, @.c varchar(128)
set @.i = 3

Did you manage to solve this out?

Satya,

Turns out that was the problem. Thanks for getting back to me on this.

Neal

|||

Nealgseattle, Satya:

Regards.

Account Lockout on SQL2005

Hi Champs,
Is there a way to recover from a account lockout in SQL2005?
I cannot connect to "Database Engine" in "SQL Server Management Studio",
error: login failed...18456.
And I also cannot start the "SQL Server agent service"
Any possebility to recover without reinstall?
/Many thanksKurlan wrote:
> Hi Champs,
> Is there a way to recover from a account lockout in SQL2005?
> I cannot connect to "Database Engine" in "SQL Server Management Studio",
> error: login failed...18456.
> And I also cannot start the "SQL Server agent service"
> Any possebility to recover without reinstall?
>
> /Many thanks
Start from here
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Regards
Amish Shah|||Hi
If your issue is that a windows account has been locked contact the system
administrator to unlock it through the active directory user management
tools. If this is a SQL login then you can uncheck the account locked box on
the status screen under the account details of the security branch in SQL
Management Studio.
You don't give any error messages that come up when you try and start SQL
agent! If you are using a domain account as the service account, then try
switching to local system and see if it will start, you can then switch back
once you have validated that the account you are using is ok.
If this does not work check out the SQL Agent Log File to see if there are
any information there.
John
"Kurlan" wrote:
> Hi Champs,
> Is there a way to recover from a account lockout in SQL2005?
> I cannot connect to "Database Engine" in "SQL Server Management Studio",
> error: login failed...18456.
> And I also cannot start the "SQL Server agent service"
> Any possebility to recover without reinstall?
>
> /Many thanks|||Yes it is the SQL account.
I cannot access SQL Management Studio, so I cannot access the SQL accounts.
"John Bell" wrote:
> Hi
> If your issue is that a windows account has been locked contact the system
> administrator to unlock it through the active directory user management
> tools. If this is a SQL login then you can uncheck the account locked box on
> the status screen under the account details of the security branch in SQL
> Management Studio.
> You don't give any error messages that come up when you try and start SQL
> agent! If you are using a domain account as the service account, then try
> switching to local system and see if it will start, you can then switch back
> once you have validated that the account you are using is ok.
> If this does not work check out the SQL Agent Log File to see if there are
> any information there.
> John
> "Kurlan" wrote:
> > Hi Champs,
> > Is there a way to recover from a account lockout in SQL2005?
> > I cannot connect to "Database Engine" in "SQL Server Management Studio",
> > error: login failed...18456.
> >
> > And I also cannot start the "SQL Server agent service"
> >
> > Any possebility to recover without reinstall?
> >
> >
> > /Many thanks|||Hi
You can also use the T-SQL command ALTER LOGIN with the UNLOCK option when
setting the password.
John
"Kurlan" wrote:
> Yes it is the SQL account.
> I cannot access SQL Management Studio, so I cannot access the SQL accounts.
>
> "John Bell" wrote:
> > Hi
> >
> > If your issue is that a windows account has been locked contact the system
> > administrator to unlock it through the active directory user management
> > tools. If this is a SQL login then you can uncheck the account locked box on
> > the status screen under the account details of the security branch in SQL
> > Management Studio.
> >
> > You don't give any error messages that come up when you try and start SQL
> > agent! If you are using a domain account as the service account, then try
> > switching to local system and see if it will start, you can then switch back
> > once you have validated that the account you are using is ok.
> >
> > If this does not work check out the SQL Agent Log File to see if there are
> > any information there.
> >
> > John
> >
> > "Kurlan" wrote:
> >
> > > Hi Champs,
> > > Is there a way to recover from a account lockout in SQL2005?
> > > I cannot connect to "Database Engine" in "SQL Server Management Studio",
> > > error: login failed...18456.
> > >
> > > And I also cannot start the "SQL Server agent service"
> > >
> > > Any possebility to recover without reinstall?
> > >
> > >
> > > /Many thanks|||in order to run a t-sql, I have to have connection to either the management
consol or via sqlcmd, and I get access denied in both cases.
"John Bell" wrote:
> Hi
> You can also use the T-SQL command ALTER LOGIN with the UNLOCK option when
> setting the password.
> John
> "Kurlan" wrote:
> > Yes it is the SQL account.
> >
> > I cannot access SQL Management Studio, so I cannot access the SQL accounts.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If your issue is that a windows account has been locked contact the system
> > > administrator to unlock it through the active directory user management
> > > tools. If this is a SQL login then you can uncheck the account locked box on
> > > the status screen under the account details of the security branch in SQL
> > > Management Studio.
> > >
> > > You don't give any error messages that come up when you try and start SQL
> > > agent! If you are using a domain account as the service account, then try
> > > switching to local system and see if it will start, you can then switch back
> > > once you have validated that the account you are using is ok.
> > >
> > > If this does not work check out the SQL Agent Log File to see if there are
> > > any information there.
> > >
> > > John
> > >
> > > "Kurlan" wrote:
> > >
> > > > Hi Champs,
> > > > Is there a way to recover from a account lockout in SQL2005?
> > > > I cannot connect to "Database Engine" in "SQL Server Management Studio",
> > > > error: login failed...18456.
> > > >
> > > > And I also cannot start the "SQL Server agent service"
> > > >
> > > > Any possebility to recover without reinstall?
> > > >
> > > >
> > > > /Many thanks|||Kurlan wrote:
> Yes it is the SQL account.
> I cannot access SQL Management Studio, so I cannot access the SQL accounts.
Why do you need Management Studio to change the service account?
Start -> Administrative Tools -> Services, go to the properties for the
MSSQLSERVICE, configure it to run as the Local System account.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
If you log in on the same machine as SQL Server is running as an
administrator you should be able to log in with a trusted connection (-E
option for SQLCMD or OSQL). You should also be able to log into management
studio this way. If not type in NET START at a command prompt to make sure
the SQL Server is running.
John
"Kurlan" wrote:
> in order to run a t-sql, I have to have connection to either the management
> consol or via sqlcmd, and I get access denied in both cases.
> "John Bell" wrote:
> > Hi
> >
> > You can also use the T-SQL command ALTER LOGIN with the UNLOCK option when
> > setting the password.
> >
> > John
> >
> > "Kurlan" wrote:
> >
> > > Yes it is the SQL account.
> > >
> > > I cannot access SQL Management Studio, so I cannot access the SQL accounts.
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > If your issue is that a windows account has been locked contact the system
> > > > administrator to unlock it through the active directory user management
> > > > tools. If this is a SQL login then you can uncheck the account locked box on
> > > > the status screen under the account details of the security branch in SQL
> > > > Management Studio.
> > > >
> > > > You don't give any error messages that come up when you try and start SQL
> > > > agent! If you are using a domain account as the service account, then try
> > > > switching to local system and see if it will start, you can then switch back
> > > > once you have validated that the account you are using is ok.
> > > >
> > > > If this does not work check out the SQL Agent Log File to see if there are
> > > > any information there.
> > > >
> > > > John
> > > >
> > > > "Kurlan" wrote:
> > > >
> > > > > Hi Champs,
> > > > > Is there a way to recover from a account lockout in SQL2005?
> > > > > I cannot connect to "Database Engine" in "SQL Server Management Studio",
> > > > > error: login failed...18456.
> > > > >
> > > > > And I also cannot start the "SQL Server agent service"
> > > > >
> > > > > Any possebility to recover without reinstall?
> > > > >
> > > > >
> > > > > /Many thanks

Saturday, February 25, 2012

account used to run SQL Server services

Account used for sql 2005, x64, " lock pages in memory" option

Account under SQL Server

Account under SQL Server

Account to Run SQL under?

Account to Run SQL under?

Account to Run SQL under?

Account to access SQL

Account problem when creating a new publication

Account lockout problem with sp_send_dbmail

Account lockout problem with sp_send_dbmail

Account Lockout on SQL2005

Account Lockout on SQL2005

SQL,access denied

Blog Archive

About Me