Regarding Full-Text search

Hello,
we have full-text search service running under local system account and we
removed Builtin/Administrators
from our logins and now our full-text catalogs are not getting populated and
now the only resolution i see is
adding NT Authority/System as a login and granting sysadmin privileges .are
there any pros and cons in doing this ..in terms of security..
Thanks In Advance!
No, basically the reason dba's remove the builtin\admin account is to ensure
that NT admin's are automatically system admins on the SQL Server box. NT
Authority/System merely gives the system account rights to SQL Server which
MSSearch requires to contact SQL Server and vice versa. With NT
Authority/System the NT admin's can no longer automatically administer your
SQL Server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5111AFEC65E20@.droptable.com...
> Hello,
> we have full-text search service running under local system account and we
> removed Builtin/Administrators
> from our logins and now our full-text catalogs are not getting populated
and
> now the only resolution i see is
> adding NT Authority/System as a login and granting sysadmin privileges
..are
> there any pros and cons in doing this ..in terms of security..
>
> Thanks In Advance!
|||Hilary,
So there are no problems in making NT Authority/System a sysadmin(SA) in sql
server
Thanks,
Hilary Cotter wrote:[vbcol=seagreen]
>No, basically the reason dba's remove the builtin\admin account is to ensure
>that NT admin's are automatically system admins on the SQL Server box. NT
>Authority/System merely gives the system account rights to SQL Server which
>MSSearch requires to contact SQL Server and vice versa. With NT
>Authority/System the NT admin's can no longer automatically administer your
>SQL Server.
>[quoted text clipped - 6 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...earch/200507/1
|||msqldba,
No there is no problem in making NT Authority/System login have sysadmin
privileges, in fact it is required if you have removed the
BUILTIN\Administrators login as the external MSSearch services needs
sysadmin privileges to access SQL Server. Note, this is by design. You can
implement this via the following T-SQL code:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Additionally, see KB article Q263712 "INF: How To Prevent Windows NT
Administrators From Administering a Clustered SQL Server" at
http://support.microsoft.com/default...;EN-US;q263712 as this KB
article recommends: "If a full-text search will be used on the cluster, you
must add the [NT Authority\System] account to the server's "sysadmin"
group."
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5116CE83ADBA0@.droptable.com...
> Hilary,
> So there are no problems in making NT Authority/System a sysadmin(SA) in
sql[vbcol=seagreen]
> server
> Thanks,
>
> Hilary Cotter wrote:
ensure[vbcol=seagreen]
which[vbcol=seagreen]
your
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...earch/200507/1

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
Thanks and Regards,
Prasanth
HI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:

> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth
|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
[vbcol=seagreen]
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
--
Thanks and Regards,
PrasanthHI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
> > Hi,
> > I am facing a problem while reading user database tables from a procedure in
> > the master database.
> > Example.
> > create procedure sp_Test
> > as
> > select count(*) from syscolumns
> > select count(*) from UserTable
> > go
> >
> > I granted public access to this procedure in the master database.
> > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > the record counts from the master tables.
> > When I execute it for some other user database, it returns the count of
> > records from syscolumn tables of that local database, but still takes the
> > count from UserTable of master database. It does not take the count from the
> > local database.
> >
> > Could anyone help me know, if there is any setting, to point to the local
> > database instead of master while refering user tables (for system tables it
> > takes from local databases). Version : SQL Server 2000
> > --
> > Thanks and Regards,
> > Prasanth|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:
> > HI
> > USE master
> > GO
> > ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> > as
> >
> > DECLARE @.strSQL as nvarchar(100)
> >
> > set @.strSQL = 'select count(*) from ' + @.UserTable
> >
> > select count(*) from syscolumns
> > EXECUTE sp_executesql @.strSQL
> >
> > GO
> >
> > USE pubs
> > GO
> > sp_test 'pubs', 'employee'
> > GO
> >
> > Andras Jakus MCDBA
> >
> > "Prasanth" wrote:
> >
> > > Hi,
> > > I am facing a problem while reading user database tables from a procedure in
> > > the master database.
> > > Example.
> > > create procedure sp_Test
> > > as
> > > select count(*) from syscolumns
> > > select count(*) from UserTable
> > > go
> > >
> > > I granted public access to this procedure in the master database.
> > > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > > the record counts from the master tables.
> > > When I execute it for some other user database, it returns the count of
> > > records from syscolumn tables of that local database, but still takes the
> > > count from UserTable of master database. It does not take the count from the
> > > local database.
> > >
> > > Could anyone help me know, if there is any setting, to point to the local
> > > database instead of master while refering user tables (for system tables it
> > > takes from local databases). Version : SQL Server 2000
> > > --
> > > Thanks and Regards,
> > > Prasanthsql