Hi DBAs,
I am very new in SQL server. I created a table where one column is varchar(8000). But when I am trying to insert value from enterprise manager this column cann't accept a long text value. I counted that its' capacity is 1012 charecters. I have tried a lot but don't know how to solve this. I really need help from you. Pls help.
Thanks in advance
Rajat RaychaudhuriI am under pressure , pls advice me soon|||Use Query Analyzer.|||Have you tried using INSERT statement from QA? What's the total length of your record?|||Thanx friends. From Query Analyzer it worked.
Rajat|||If you are new to this, one quick note. SQL Server's Row Byte size is limited somewhere right around 8k bytes for physical data stored in the database. So, if the table has other columns in it, you might be in for trouble. Switching a very large column to Text may save you some future hassle. Of course, it could cause you a little hassle right now. :)|||I respect people that appreciate the value of TEXT/IMAGE datatypes ;)|||A little hassle now? That's not near as much hassle as it could cause him down the road.
RANT OFF
Go ahead...your turn now.|||TEXT/NTEXT/IMAGE columns definitely have their place. They can do things that are otherwise impossible in SQL Server. I don't see them as substitutes for VARCHAR or NVARCHAR any more than I see DATETIME as a substitute... Under certain rigorous conditions any of them might work, but as a generic substitute they are poor choices.
-PatP|||Switching a very large column to Text
-----------
http://www.3-ibm.com|||Is this a question? And how large is "very large"?|||I think he means it's veeeeeeeeeeeeeeeery large.
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Long disappeared
I have just created a new database in the same computer that has a a one other databases. The other database has the "long" datatype for values but the new one doesn't allow them to be added. It's not on the list for datatypes. What setting am I missing?I am not aware of SQL Server ever allowing long as a datatype. Int and BIG_INT are allowed integer types, int is a 4 byte value, BIG_INT is 8 bytes, as I recall.|||I'm a big fat Mr. Dumbaz... Thanks. I was thinking C# not SQL.|||"long" is an Oracle datatype. It is functionally equivalent to "text" datatype in SQL Server.
Wednesday, March 21, 2012
LogShipping
Hi all I have created a log shipping task in my SQL servers
This Is the server order
Ist Server (Win2003 , SQL 2000 sp3) is the data server let's say
2nd Server (win2003 , SQL 2000 sp3) Is the monitor server
3rd Server (win2000, SQL 2000 sp3) Is the server that accepts the data
(1st & 2nd server is a clucter system )
And the line Connecting 1st & 2nd Server with the 3rd server is 1MB
The problem now is that the SQL Help is awful about logshipping. It explains
nothing!!
I have Create the log shipping but i get en error that the databases are not
sync.
And the FileLoaded and file copped is stack on First_File...trn
And also my databades seemed to be in sync
What Is wrong'
What should I do'
Please HELP!!!
Thanks in advance
DimitrisHi,
I have to correct you on something...there is plenty of
documentation for log shipping!
Try looking at BOL and this link will help. You should
read before implementing.
http://support.microsoft.com/?id=323135
hth
DeeJay
>--Original Message--
>Hi all I have created a log shipping task in my SQL
servers
>This Is the server order
>Ist Server (Win2003 , SQL 2000 sp3) is the data server
let's say
>2nd Server (win2003 , SQL 2000 sp3) Is the monitor server
>3rd Server (win2000, SQL 2000 sp3) Is the server that
accepts the data
>(1st & 2nd server is a clucter system )
>And the line Connecting 1st & 2nd Server with the 3rd
server is 1MB
>The problem now is that the SQL Help is awful about
logshipping. It explains
>nothing!!
>I have Create the log shipping but i get en error that
the databases are not
>sync.
>And the FileLoaded and file copped is stack on
First_File...trn
>And also my databades seemed to be in sync
>What Is wrong'
>What should I do'
>Please HELP!!!
>Thanks in advance
>Dimitris
>
>
>.
>
This Is the server order
Ist Server (Win2003 , SQL 2000 sp3) is the data server let's say
2nd Server (win2003 , SQL 2000 sp3) Is the monitor server
3rd Server (win2000, SQL 2000 sp3) Is the server that accepts the data
(1st & 2nd server is a clucter system )
And the line Connecting 1st & 2nd Server with the 3rd server is 1MB
The problem now is that the SQL Help is awful about logshipping. It explains
nothing!!
I have Create the log shipping but i get en error that the databases are not
sync.
And the FileLoaded and file copped is stack on First_File...trn
And also my databades seemed to be in sync
What Is wrong'
What should I do'
Please HELP!!!
Thanks in advance
DimitrisHi,
I have to correct you on something...there is plenty of
documentation for log shipping!
Try looking at BOL and this link will help. You should
read before implementing.
http://support.microsoft.com/?id=323135
hth
DeeJay
>--Original Message--
>Hi all I have created a log shipping task in my SQL
servers
>This Is the server order
>Ist Server (Win2003 , SQL 2000 sp3) is the data server
let's say
>2nd Server (win2003 , SQL 2000 sp3) Is the monitor server
>3rd Server (win2000, SQL 2000 sp3) Is the server that
accepts the data
>(1st & 2nd server is a clucter system )
>And the line Connecting 1st & 2nd Server with the 3rd
server is 1MB
>The problem now is that the SQL Help is awful about
logshipping. It explains
>nothing!!
>I have Create the log shipping but i get en error that
the databases are not
>sync.
>And the FileLoaded and file copped is stack on
First_File...trn
>And also my databades seemed to be in sync
>What Is wrong'
>What should I do'
>Please HELP!!!
>Thanks in advance
>Dimitris
>
>
>.
>
Logreader agent not being created
I'm working with a client on a problem where they setup transactional
replication publications, push out a subscription, the snap shots are
created, the data is initially pushed out to the subscriber but no Logreader
agent is created - So no transactional replication occurs.
The Sql server, agent, and application and system logs show no errors.
If I script out the replication the sp_addpublication is being called
without the @.logreader_job_name parameter. I could try installing the agent
manually using logread.exe but I want to understand why the agent is not
being created the way it normally should.
Any help would be appreciated,
J
can you script out your publication and post it here.
Also sometimes the agent folders get a little scrambled and the agents don't
show up there. Could you perhaps check to see if there aren't any logreader
processes showing up in task manger, or check to see if you can create
another transactional publication in a different database. Make sure you use
a unique name for the publication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uKsSQxAqEHA.376@.TK2MSFTNGP14.phx.gbl...
> I'm working with a client on a problem where they setup transactional
> replication publications, push out a subscription, the snap shots are
> created, the data is initially pushed out to the subscriber but no
Logreader
> agent is created - So no transactional replication occurs.
> The Sql server, agent, and application and system logs show no errors.
> If I script out the replication the sp_addpublication is being called
> without the @.logreader_job_name parameter. I could try installing the
agent
> manually using logread.exe but I want to understand why the agent is not
> being created the way it normally should.
> Any help would be appreciated,
> J
>
|||Thank's for the response,
Attached is the replication script with the beginning part that sets up the
distributor and the setup for one of the publications that is failing to
create a Log Reader agent.
As you can see the logreader_job_name parameter is absent. Also I checked in
the server and the logread.exe executable is there in its proper place.
The server is running Sql Server 2000 Standard SP3 on Microsoft Windows
Server 2003 Standard, replicating to a server with the same configuraton
(except 2500 miles away).
This same replication worked just fine on previous servers that the
application was migrated from: Sql Server 7.0 on Windows 2000 Server
Advanced (clustered).
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ec33qHCqEHA.3464@.TK2MSFTNGP14.phx.gbl...
> can you script out your publication and post it here.
> Also sometimes the agent folders get a little scrambled and the agents
don't
> show up there. Could you perhaps check to see if there aren't any
logreader
> processes showing up in task manger, or check to see if you can create
> another transactional publication in a different database. Make sure you
use
> a unique name for the publication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:uKsSQxAqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Logreader
> agent
>
begin 666 Assoc.sql
M+RHJ*BHJ*B!38W)I<'1I;F<@.=&AE(')E<&QI8V%T:6]N('-E='5P(&]F('-E
M<G9E<B!-0E!34TE344PP,2X@.4V-R:7!T($1A=&4Z(#$P+S$O,C P-" S.C0Y
M.C(X(%!-("HJ*BHJ*B\-"B\J*BHJ*BH@.4&QE87-E(&YO=&4Z($%N>2!P87-S
M=V]R9"!P87)A;65T97(@.=V%S('-C<FEP=&5D('=I=&@.@.3E5,3"!O<B!E;7!T
M>2!S=')I;F<@.9F]R('-E8W5R:71Y(')E87-O;BX@.*BHJ*BHJ+PT*#0HO*BHJ
M*BHJ($)E9VEN.B!38W)I<'0@.=&\@.8F4@.<G5N(&%T($1I<W1R: 6)U=&]R.B!-
M0E!34TE344PP,2 J*BHJ*BHO#0HO*BHJ*BHJ($EN<W1A;&QI;F<@.=&AE('-E
M<G9E<B!-0E!34TE344PP,2!A<R!A($1I<W1R:6)U=&]R+B!38W)I<'0@.1&%T
M93H@.,3 O,2\R,# T(#,Z-#DZ,C@.@.4$T@.*BHJ*BHJ+PT*=7-E(&UA<W1E<@.T*
M1T\-"@.T*97AE8R!S<%]A9&1D:7-T<FEB=71O<B @.0&1I<W1R:6)U=&]R(#T@.
M3B=-0E!34TE344PP,2<L($!P87-S=V]R9" ]($XG)PT*1T\-"@.T*+2T@.57!D
M871I;F<@.=&AE(&%G96YT('!R;V9I;&4@.9&5F875L=',-"G-P7TU3=7!D871E
M7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE7VED(#T@.,0T*1T\-"G-P7TU3
M=7!D871E7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE7VED(#T@.,@.T*1T\-
M"G-P7TU3=7!D871E7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE7VED(#T@.
M- T*1T\-"G-P7TU3=7!D871E7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE
M7VED(#T@.-@.T*1T\-"G-P7TU3=7!D871E7V%G96YT='EP95]D969A=6QT($!P
M<F]F:6QE7VED(#T@.,3$-"D=/#0H-"BTM($%D9&EN9R!T:&4@.9&ES=')I8G5T
M:6]N(&1A=&%B87-E#0IE>&5C('-P7V%D9&1I<W1R:6)U=&EO;F1B("! 9&%T
M86)A<V4@./2!.)V1I<W1R:6)U=&EO;B<L($!D871A7V9O;&1E<B ]($XG13I<
M4U%,,DM<35-344Q$051!7$U34U%,7$1A=&$G+"! 9&%T85]F:6QE(#T@.3B=D
M:7-T<FEB=71I;VXN341&)RP@.0&1A=&%?9FEL95]S:7IE(#T@.,S,S+"! ;&]G
M7V9O;&1E<B ]($XG13I<4U%,,DM<35-344Q$051!7$U34U%,7$1A=&$G+"!
M;&]G7V9I;&4@./2!.)V1I<W1R:6)U=&EO;BY,1$8G+"! ;&]G7V9I;&5?<VEZ
M92 ](#4V+"! ;6EN7V1I<W1R971E;G1I;VX@./2 P+"! ;6%X7V1I<W1R971E
M;G1I;VX@./2 W,BP@.0&AI<W1O<GE?<F5T96YT:6]N(#T@.-#@.L($!S96-U<FET
M>5]M;V1E(#T@.,0T*1T\-"@.T*+2T@.061D:6YG('1H92!D:7-T<FEB=71I;VX@.
M<'5B;&ES:&5R#0IE>&5C('-P7V%D9&1I<W1P=6)L:7-H97(@.($!P=6)L:7-H
M97(@./2!.)TU"4%-325-13# Q)RP@.0&1I<W1R:6)U=&EO;E]D8B ]($XG9&ES
M=')I8G5T:6]N)RP@.0'-E8W5R:71Y7VUO9&4@./2 Q+"! =V]R:VEN9U]D:7)E
M8W1O<GD@./2!.)UQ<34)04U-)4U%,,#%<4D503$1!5$$G+"! =')U<W1E9" ]
M($XG9F%L<V4G+"! =&AI<F1P87)T>5]F;&%G(#T@., T*1T\-"@.T*+RHJ*BHJ
M*B!%;F0Z(%-C<FEP="!T;R!B92!R=6X@.870@.1&ES=')I8G5T;W(Z($U"4%-3
M25-13# Q("HJ*BHJ*B\-"@.T*+RHJ*BHJ*B!"96=I;CH@.4V-R:7!T('1O(&)E
M(')U;B!A="!0=6)L:7-H97(Z($U"4%-325-13# Q("HJ*BHJ*B\-"BTM($%D
M9&EN9R!T:&4@.<F5G:7-T97)E9"!S=6)S8W)I8F5R#0IE>&5C('-P7V%D9'-U
M8G-C<FEB97(@.0'-U8G-C<FEB97(@./2!.)TA"4%-325-13# Q)RP@.0'1Y<&4@.
M/2 P+"! <V5C=7)I='E?;6]D92 ](#$L($!F<F5Q=65N8WE?='EP92 ](#8T
M+"! 9G)E<75E;F-Y7VEN=&5R=F%L(#T@.,2P@.0&9R97%U96YC>5]R96QA=&EV
M95]I;G1E<G9A;" ](#(L($!F<F5Q=65N8WE?<F5C=7)R96YC95]F86-T;W(@.
M/2 P+"! 9G)E<75E;F-Y7W-U8F1A>2 ](#@.L($!F<F5Q=65N8WE?<W5B9&%Y
M7VEN=&5R=F%L(#T@.,2P@.0&%C=&EV95]S=&%R=%]D871E(#T@.,"P@.0&%C=&EV
M95]E;F1?9&%T92 ](# L($!A8W1I=F5?<W1A<G1?=&EM95]O9E]D87D@./2 P
M+"! 86-T:79E7V5N9%]T:6UE7V]F7V1A>2 ](#(S-3DP,"P@.0&1E<V-R:7!T
M:6]N(#T@.3B<G#0H@.97AE8R!S<%]C:&%N9V5S=6)S8W)I8F5R7W-C:&5D=6QE
M($!S=6)S8W)I8F5R(#T@.3B=(0E!34TE344PP,2<L($!A9V5N= %]T>7!E(#T@.
M,2P@.0&%C=&EV95]E;F1?9&%T92 ](# -"D=/#0H-"BTM($5N86)L:6YG('1H
M92!R97!L:6-A=&EO;B!D871A8F%S90T*=7-E(&UA<W1E<@.T*1T\-"@.T*97AE
M8R!S<%]R97!L:6-A=&EO;F1B;W!T:6]N($!D8FYA;64@./2!.)T]21$5214Y4
M4EDG+"! ;W!T;F%M92 ]($XG<'5B;&ES:"<L($!V86QU92 ]($XG=')U92<-
M"D=/#0H-"G5S92!;3U)$15)%3E1265T-"D=/#0H-"BTM($%D9&EN9R!T:&4@.
M=')A;G-A8W1I;VYA;"!P=6)L:6-A=&EO;@.T*97AE8R!S<%]A9&1P=6)L:6-A
M=&EO;B! <'5B;&EC871I;VX@./2!.)T%S<V]C7TQO9R<L($!R97-T<FEC=&5D
M(#T@.3B=F86QS92<L($!S>6YC7VUE=&AO9" ]($XG;F%T:79E)RP@.0')E<&Q?
M9G)E<2 ]($XG8V]N=&EN=6]U<R<L($!D97-C<FEP=&EO;B ]($XG5')A;G-A
M8W1I;VYA;"!P=6)L:6-A=&EO;B!O9B!/4D1%4D5.5%)9(&1A=&%B87-E(&9R
M;VT@.4'5B;&ES:&5R($U"4%-325-13# Q+B<L($!S=&%T=7,@./2!.)V%C=&EV
M92<L($!A;&QO=U]P=7-H(#T@.3B=T<G5E)RP@.0&%L;&]W7W!U;&P@./2!.)W1R
M=64G+"! 86QL;W=?86YO;GEM;W5S(#T@.3B=F86QS92<L($!E;F%B;&5D7V 9O
M<E]I;G1E<FYE=" ]($XG9F%L<V4G+"! :6YD97!E;F1E;G1?86=E;G0@./2!.
M)W1R=64G+"! :6UM961I871E7W-Y;F,@./2!.)V9A;'-E)RP@.0&%L;&]W7W-Y
M;F-?=')A;B ]($XG=')U92<L($!A=71O9V5N7W-Y;F-?<')O8W,@./2!.)W1R
M=64G+"! <F5T96YT:6]N(#T@.,S,V+"! 86QL;W=?<75E=65D7W1R86X@./2!.
M)V9A;'-E)RP@.0'-N87!S:&]T7VEN7V1E9F%U;'1F;VQD97(@./2!.)W1R=64G
M+"! 8V]M<')E<W-?<VYA<'-H;W0@./2!.)V9A;'-E)RP@.0&9T<%]P;W)T(#T@.
M,C$L($!F='!?;&]G:6X@./2!.)V%N;VYY;6]U<R<L($!A;&QO=U]D=',@./2!.
M)V9A;'-E)RP@.0&%L;&]W7W-U8G-C<FEP=&EO;E]C;W!Y(#T@.3B=F86QS92<L
M($!A9&1?=&]?86-T:79E7V1I<F5C=&]R>2 ]($XG9F%L<V4G+"! <7)E861E
M<E]J;V)?;F%M92 ]($XG6TU"4%-325-13# Q72XV)PT*97AE8R!S<%]A9&1P
M=6)L:6-A=&EO;E]S;F%P<VAO="! <'5B;&EC871I;VX@./2!.)T%S<V]C7TQO
M9R<L0&9R97%U96YC>5]T>7!E(#T@.-"P@.0&9R97%U96YC>5]I;G1E<G9A;" ]
M(#$L($!F<F5Q=65N8WE?<F5L871I=F5?:6YT97)V86P@./2 Q+"! 9G)E<75E
M;F-Y7W)E8W5R<F5N8V5?9F%C=&]R(#T@.,"P@.0&9R97%U96YC>5]S=6)D87D@.
M/2 X+"! 9G)E<75E;F-Y7W-U8F1A>5]I;G1E<G9A;" ](#$L($!A8W1I=F5?
M<W1A<G1?9&%T92 ](# L($!A8W1I=F5?96YD7V1A=&4@./2 P+"! 86-T:79E
M7W-T87)T7W1I;65?;V9?9&%Y(#T@.,"P@.0&%C=&EV95]E;F1?=&EM95]O9E]D
M87D@./2 R,S4Y-3DL($!S;F%P<VAO=%]J;V)?;F%M92 ]($XG34)04U-)4U%,
M,#$M3U)$15)%3E1262U!<W-O8U],;V<M,34G#0I'3PT*#0IE>&5C('-P7V=R
M86YT7W!U8FQI8V%T:6]N7V%C8V5S<R! <'5B;&EC871I;VX@./2!.)T%S<V]C
M7TQO9R<L($!L;V=I;B ]($XG0E5)3%1)3EQ!9&UI;FES=')A=&]R<R<-"D=/
M#0IE>&5C('-P7V=R86YT7W!U8FQI8V%T:6]N7V%C8V5S<R! <'5B;&EC871I
M;VX@./2!.)T%S<V]C7TQO9R<L($!L;V=I;B ]($XG9&ES=')I8G5T;W)?861M
M:6XG#0I'3PT*97AE8R!S<%]G<F%N=%]P=6)L:6-A=&EO;E]A8V-E<W,@.0'!U
M8FQI8V%T:6]N(#T@.3B=!<W-O8U],;V<G+"! ;&]G:6X@./2!.)W-A)PT*1T\-
M"@.T*+2T@.061D:6YG('1H92!T<F%N<V%C=&EO;F%L(&%R=&EC; &5S#0IE>&5C
M('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T:6]N(#T@.3B=!<W-O8U],;V<G+"!
M87)T:6-L92 ]($XG;&]G7V%T=&5N9&%N8V4G+"! <V]U<F-E7V]W;F5R(#T@.
M3B=D8F\G+"! <V]U<F-E7V]B:F5C=" ]($XG;&]G7V%T=&5N9&%N8V4G+"!
M9&5S=&EN871I;VY?=&%B;&4@./2!.)VQO9U]A='1E;F1A;F-E)RP@.0'1Y<&4@.
M/2!.)VQO9V)A<V5D)RP@.0&-R96%T:6]N7W-C<FEP=" ](&YU;&PL($!D97-C
M<FEP=&EO;B ](&YU;&PL($!P<F5?8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.
M0'-C:&5M85]O<'1I;VX@./2 P># P,# P,# P,# P,$-%1C,L($!S=&%T=7,@.
M/2 Q-BP@.0'9E<G1I8V%L7W!A<G1I=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M
M9" ]($XG0T%,3"!S<%]-4VEN<U]L;V=?871T96YD86YC92<L($!D96Q?8VUD
M(#T@.3B=#04Q,('-P7TU39&5L7VQO9U]A='1E;F1A;F-E)RP@.0'5P9%]C;60@.
M/2!.)TU#04Q,('-P7TU3=7!D7VQO9U]A='1E;F1A;F-E)RP@.0&9I;'1E<B ]
M(&YU;&PL($!S>6YC7V]B:F5C=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A
M;F=E(#T@.3B=F86QS92<-"D=/#0IE>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI
M8V%T:6]N(#T@.3B=!<W-O8U],;V<G+"! 87)T:6-L92 ]($XG;&]G7V1E<&5N
M9&5N=',G+"! <V]U<F-E7V]W;F5R(#T@.3B=D8F\G+"! <V]U<F-E7V]B:F5C
M=" ]($XG;&]G7V1E<&5N9&5N=',G+"! 9&5S=&EN871I;VY?=&%B;&4@./2!.
M)VQO9U]D97!E;F1E;G1S)RP@.0'1Y<&4@./2!.)VQO9V)A<V5D)RP@.0&-R96%T
M:6]N7W-C<FEP=" ](&YU;&PL($!D97-C<FEP=&EO;B ](&YU;&PL($!P<F5?
M8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.0'-C:&5M85]O<'1I;VX@./2 P># P
M,# P,# P,# P,$-%1C,L($!S=&%T=7,@./2 Q-BP@.0'9E<G1I8V%L7W!A<G1I
M=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M9" ]($XG0T%,3"!S<%]-4VEN<U]L
M;V=?9&5P96YD96YT<R<L($!D96Q?8VUD(#T@.3B=#04Q,('-P7TU39&5L7VQO
M9U]D97!E;F1E;G1S)RP@.0'5P9%]C;60@./2!.)TU#04Q,('-P7TU3=7!D7VQO
M9U]D97!E;F1E;G1S)RP@.0&9I;'1E<B ](&YU;&PL($!S>6YC7V]B:F5C=" ]
M(&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E(#T@.3B=F86QS9 2<-"D=/#0IE
M>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T:6]N(#T@.3B=!<W-O8U],;V<G
M+"! 87)T:6-L92 ]($XG;&]G7V1E=&%I;',G+"! <V]U<F-E7V]W;F5R(#T@.
M3B=D8F\G+"! <V]U<F-E7V]B:F5C=" ]($XG;&]G7V1E=&%I;',G+"! 9&5S
M=&EN871I;VY?=&%B;&4@./2!.)VQO9U]D971A:6QS)RP@.0'1Y<&4@./2!.)VQO
M9V)A<V5D)RP@.0&-R96%T:6]N7W-C<FEP=" ](&YU;&PL($!D97-C<FEP=&EO
M;B ](&YU;&PL($!P<F5?8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.0'-C:&5M
M85]O<'1I;VX@./2 P># P,# P,# P,# P,$-%1C,L($!S=&%T=7,@./2 Q-BP@.
M0'9E<G1I8V%L7W!A<G1I=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M9" ]($XG
M0T%,3"!S<%]-4VEN<U]L;V=?9&5T86EL<R<L($!D96Q?8VUD(#T@.3B=#04Q,
M('-P7TU39&5L7VQO9U]D971A:6QS)RP@.0'5P9%]C;60@./2!.)TU#04Q,('-P
M7TU3=7!D7VQO9U]D971A:6QS)RP@.0&9I;'1E<B ](&YU;&PL($!S>6YC7V]B
M:F5C=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E(#T@.3B=F86QS92 <-
M"D=/#0IE>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T:6]N(#T@.3B=!<W-O
M8U],;V<G+"! 87)T:6-L92 ]($XG;&]G7VYO4F5H:7)E)RP@.0'-O=7)C95]O
M=VYE<B ]($XG9&)O)RP@.0'-O=7)C95]O8FIE8W0@./2!.)VQO9U]N;U)E:&ER
M92<L($!D97-T:6YA=&EO;E]T86)L92 ]($XG;&]G7VYO4F5H:7)E)RP@.0'1Y
M<&4@./2!.)VQO9V)A<V5D)RP@.0&-R96%T:6]N7W-C<FEP=" ](&YU;&PL($!D
M97-C<FEP=&EO;B ](&YU;&PL($!P<F5?8W)E871I;VY?8VUD(#T@.3B=D<F]P
M)RP@.0'-C:&5M85]O<'1I;VX@./2 P># P,# P,# P,# P,$-%1C,L($!S=&%T
M=7,@./2 Q-BP@.0'9E<G1I8V%L7W!A<G1I=&EO;B ]($XG9F%L<V4G+"! :6YS
M7V-M9" ]($XG0T%,3"!S<%]-4VEN<U]L;V=?;F]296AI<F4G+"! 9&5L7V-M
M9" ]($XG0T%,3"!S<%]-4V1E;%]L;V=?;F]296AI<F4G+"! =7!D7V-M9" ]
M($XG34-!3$P@.<W!?35-U<&1?;&]G7VYO4F5H:7)E)RP@.0&9I;'1E<B ](&YU
M;&PL($!S>6YC7V]B:F5C=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E
M(#T@.3B=F86QS92<-"D=/#0IE>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T
M:6]N(#T@.3B=!<W-O8U],;V<G+"! 87)T:6-L92 ]($XG;&]G7W-E<&%R871I
M;VYS)RP@.0'-O=7)C95]O=VYE<B ]($XG9&)O)RP@.0'-O=7)C95]O8FIE8W0@.
M/2!.)VQO9U]S97!A<F%T:6]N<R<L($!D97-T:6YA=&EO;E]T86)L92 ]($XG
M;&]G7W-E<&%R871I;VYS)RP@.0'1Y<&4@./2!.)VQO9V)A<V5D)RP@.0&-R96%T
M:6]N7W-C<FEP=" ](&YU;&PL($!D97-C<FEP=&EO;B ](&YU;&PL($!P<F5?
M8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.0'-C:&5M85]O<'1I;VX@./2 P># P
M,# P,# P,# P,$-%1C,L($!S=&%T=7,@./2 Q-BP@.0'9E<G1I8V%L7W!A<G1I
M=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M9" ]($XG0T%,3"!S<%]-4VEN<U]L
M;V=?<V5P87)A=&EO;G,G+"! 9&5L7V-M9" ]($XG0T%,3"!S<%]-4V1E;%]L
M;V=?<V5P87)A=&EO;G,G+"! =7!D7V-M9" ]($XG34-!3$P@.<W!?35-U<&1?
M;&]G7W-E<&%R871I;VYS)RP@.0&9I;'1E<B ](&YU;&PL($!S>6YC7V]B:F5C
M=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E(#T@.3B=F86QS92 <-"D=/
M#0H-"BTM($%D9&EN9R!T:&4@.=')A;G-A8W1I;VYA;"!S=6)S8W)I<'1I;VX-
M"F5X96,@.<W!?861D<W5B<V-R:7!T:6]N($!P=6)L:6-A=&EO;B ]($XG07-S
M;V-?3&]G)RP@.0&%R=&EC;&4@./2!.)V%L;"<L($!S=6)S8W)I8F5R(#T@.3B=(
M0E!34TE344PP,2<L($!D97-T:6YA=&EO;E]D8B ]($XG3U)$15)%3E1262<L
M($!S>6YC7W1Y<&4@./2!.)V%U=&]M871I8R<L($!U<&1A=&5?;6]D92 ]($XG
M<WEN8R!T<F%N)RP@.0&]F9FQO861A9V5N=" ](# L($!D='-?<&%C:V%G95]L
<;V-A=&EO;B ]($XG9&ES=')I8G5T;W(G#0I'3P``
`
end
|||You are using Updateable Subscriptions with Queued Updating.
the log reader agent name should (but doesn't have to be blank). Any value
you put in here will be ignored.
I just want to confirm that the log reader agent is not showing up in the
publisher. It should not show up on the subscriber.
Also can you check your jobs folder (under the management folder) to see if
a log reader agent/job is not created for this job,
Sometimes the agents will not show up in the replication agents folder but
are actually there, and you can only see them in the jobs folder. When this
happens it is difficult to administer your agents, but you will have a
working solution.
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:%238DZmWDqEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Thank's for the response,
> Attached is the replication script with the beginning part that sets up
> the
> distributor and the setup for one of the publications that is failing to
> create a Log Reader agent.
> As you can see the logreader_job_name parameter is absent. Also I checked
> in
> the server and the logread.exe executable is there in its proper place.
> The server is running Sql Server 2000 Standard SP3 on Microsoft Windows
> Server 2003 Standard, replicating to a server with the same configuraton
> (except 2500 miles away).
> This same replication worked just fine on previous servers that the
> application was migrated from: Sql Server 7.0 on Windows 2000 Server
> Advanced (clustered).
> Jim
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ec33qHCqEHA.3464@.TK2MSFTNGP14.phx.gbl...
> don't
> logreader
> use
>
>
|||It was setup for Immediate Updating and Queued Updating as a failover. Even
still, doesn't there need to be a Log Reader agent to pickup any
transactions to replicate?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZ48JFJqEHA.3244@.tk2msftngp13.phx.gbl...
> You are using Updateable Subscriptions with Queued Updating.
> the log reader agent name should (but doesn't have to be blank). Any value
> you put in here will be ignored.
> I just want to confirm that the log reader agent is not showing up in the
> publisher. It should not show up on the subscriber.
> Also can you check your jobs folder (under the management folder) to see
if
> a log reader agent/job is not created for this job,
> Sometimes the agents will not show up in the replication agents folder but
> are actually there, and you can only see them in the jobs folder. When
this[vbcol=seagreen]
> happens it is difficult to administer your agents, but you will have a
> working solution.
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%238DZmWDqEHA.3668@.TK2MSFTNGP15.phx.gbl...
checked[vbcol=seagreen]
you[vbcol=seagreen]
errors.
>
|||The log reader agent is not showing up at the publisher or the subscriber.
There is also no agent job listed.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZ48JFJqEHA.3244@.tk2msftngp13.phx.gbl...
> You are using Updateable Subscriptions with Queued Updating.
> the log reader agent name should (but doesn't have to be blank). Any value
> you put in here will be ignored.
> I just want to confirm that the log reader agent is not showing up in the
> publisher. It should not show up on the subscriber.
> Also can you check your jobs folder (under the management folder) to see
if
> a log reader agent/job is not created for this job,
> Sometimes the agents will not show up in the replication agents folder but
> are actually there, and you can only see them in the jobs folder. When
this[vbcol=seagreen]
> happens it is difficult to administer your agents, but you will have a
> working solution.
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%238DZmWDqEHA.3668@.TK2MSFTNGP15.phx.gbl...
checked[vbcol=seagreen]
you[vbcol=seagreen]
errors.
>
|||yes, there will be a log reader agent on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:%23oQdcIKqEHA.1576@.TK2MSFTNGP12.phx.gbl...
> It was setup for Immediate Updating and Queued Updating as a failover.
Even[vbcol=seagreen]
> still, doesn't there need to be a Log Reader agent to pickup any
> transactions to replicate?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZ48JFJqEHA.3244@.tk2msftngp13.phx.gbl...
value[vbcol=seagreen]
the[vbcol=seagreen]
> if
but[vbcol=seagreen]
> this
up[vbcol=seagreen]
to[vbcol=seagreen]
> checked
place.[vbcol=seagreen]
Windows[vbcol=seagreen]
configuraton[vbcol=seagreen]
agents[vbcol=seagreen]
create[vbcol=seagreen]
> you
transactional[vbcol=seagreen]
are[vbcol=seagreen]
> errors.
called[vbcol=seagreen]
the[vbcol=seagreen]
is
>
|||I tried your script. It works on my machine. I think there is something
wrong with your machine. Can you try to disable replication, recreate it,
and then recreate your publication and subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uFdQI6KqEHA.3712@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> yes, there will be a log reader agent on the publisher.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23oQdcIKqEHA.1576@.TK2MSFTNGP12.phx.gbl...
> Even
> value
> the
see[vbcol=seagreen]
> but
[vbcol=seagreen]
> up
failing[vbcol=seagreen]
> to
> place.
> Windows
> configuraton
> agents
> create
sure[vbcol=seagreen]
> transactional
> are
no
> called
> the
> is
>
|||That's what I feared. There is definitely something wrong with the way
replication was installed on this box. Unfortunately I was not the one who
installed Sql Server and replication. I was called in after the fact when it
looked like replication was not working. It may be an issue of permissions
and rights during the setup of replication on the server, as this company
does not want to give the Sql Server DBAs admin rights to their servers.
Thanks for all your help!
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ejcXuBOqEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I tried your script. It works on my machine. I think there is something
> wrong with your machine. Can you try to disable replication, recreate it,
> and then recreate your publication and subscription.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uFdQI6KqEHA.3712@.TK2MSFTNGP15.phx.gbl...
in[vbcol=seagreen]
> see
folder[vbcol=seagreen]
When[vbcol=seagreen]
a[vbcol=seagreen]
sets[vbcol=seagreen]
> failing
the[vbcol=seagreen]
Server[vbcol=seagreen]
> sure
shots[vbcol=seagreen]
> no
installing[vbcol=seagreen]
agent
>
|||I don't think it is a problem with the way the previous dba installed
replication, rather there is something wrong with the SQL Server
environment; ie missing stored procedures, or an inconsistent application of
SP 3. I suggest you reapply SP 3 and try again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uiosaiOqEHA.2588@.TK2MSFTNGP12.phx.gbl...
> That's what I feared. There is definitely something wrong with the way
> replication was installed on this box. Unfortunately I was not the one who
> installed Sql Server and replication. I was called in after the fact when
it[vbcol=seagreen]
> looked like replication was not working. It may be an issue of permissions
> and rights during the setup of replication on the server, as this company
> does not want to give the Sql Server DBAs admin rights to their servers.
> Thanks for all your help!
> Jim
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ejcXuBOqEHA.3244@.tk2msftngp13.phx.gbl...
it,[vbcol=seagreen]
failover.[vbcol=seagreen]
Any[vbcol=seagreen]
> in
to[vbcol=seagreen]
> folder
> When
have[vbcol=seagreen]
> a
> sets
I[vbcol=seagreen]
> the
> Server
any[vbcol=seagreen]
> shots
but[vbcol=seagreen]
no[vbcol=seagreen]
being
> installing
> agent
>
replication publications, push out a subscription, the snap shots are
created, the data is initially pushed out to the subscriber but no Logreader
agent is created - So no transactional replication occurs.
The Sql server, agent, and application and system logs show no errors.
If I script out the replication the sp_addpublication is being called
without the @.logreader_job_name parameter. I could try installing the agent
manually using logread.exe but I want to understand why the agent is not
being created the way it normally should.
Any help would be appreciated,
J
can you script out your publication and post it here.
Also sometimes the agent folders get a little scrambled and the agents don't
show up there. Could you perhaps check to see if there aren't any logreader
processes showing up in task manger, or check to see if you can create
another transactional publication in a different database. Make sure you use
a unique name for the publication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uKsSQxAqEHA.376@.TK2MSFTNGP14.phx.gbl...
> I'm working with a client on a problem where they setup transactional
> replication publications, push out a subscription, the snap shots are
> created, the data is initially pushed out to the subscriber but no
Logreader
> agent is created - So no transactional replication occurs.
> The Sql server, agent, and application and system logs show no errors.
> If I script out the replication the sp_addpublication is being called
> without the @.logreader_job_name parameter. I could try installing the
agent
> manually using logread.exe but I want to understand why the agent is not
> being created the way it normally should.
> Any help would be appreciated,
> J
>
|||Thank's for the response,
Attached is the replication script with the beginning part that sets up the
distributor and the setup for one of the publications that is failing to
create a Log Reader agent.
As you can see the logreader_job_name parameter is absent. Also I checked in
the server and the logread.exe executable is there in its proper place.
The server is running Sql Server 2000 Standard SP3 on Microsoft Windows
Server 2003 Standard, replicating to a server with the same configuraton
(except 2500 miles away).
This same replication worked just fine on previous servers that the
application was migrated from: Sql Server 7.0 on Windows 2000 Server
Advanced (clustered).
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ec33qHCqEHA.3464@.TK2MSFTNGP14.phx.gbl...
> can you script out your publication and post it here.
> Also sometimes the agent folders get a little scrambled and the agents
don't
> show up there. Could you perhaps check to see if there aren't any
logreader
> processes showing up in task manger, or check to see if you can create
> another transactional publication in a different database. Make sure you
use
> a unique name for the publication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:uKsSQxAqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Logreader
> agent
>
begin 666 Assoc.sql
M+RHJ*BHJ*B!38W)I<'1I;F<@.=&AE(')E<&QI8V%T:6]N('-E='5P(&]F('-E
M<G9E<B!-0E!34TE344PP,2X@.4V-R:7!T($1A=&4Z(#$P+S$O,C P-" S.C0Y
M.C(X(%!-("HJ*BHJ*B\-"B\J*BHJ*BH@.4&QE87-E(&YO=&4Z($%N>2!P87-S
M=V]R9"!P87)A;65T97(@.=V%S('-C<FEP=&5D('=I=&@.@.3E5,3"!O<B!E;7!T
M>2!S=')I;F<@.9F]R('-E8W5R:71Y(')E87-O;BX@.*BHJ*BHJ+PT*#0HO*BHJ
M*BHJ($)E9VEN.B!38W)I<'0@.=&\@.8F4@.<G5N(&%T($1I<W1R: 6)U=&]R.B!-
M0E!34TE344PP,2 J*BHJ*BHO#0HO*BHJ*BHJ($EN<W1A;&QI;F<@.=&AE('-E
M<G9E<B!-0E!34TE344PP,2!A<R!A($1I<W1R:6)U=&]R+B!38W)I<'0@.1&%T
M93H@.,3 O,2\R,# T(#,Z-#DZ,C@.@.4$T@.*BHJ*BHJ+PT*=7-E(&UA<W1E<@.T*
M1T\-"@.T*97AE8R!S<%]A9&1D:7-T<FEB=71O<B @.0&1I<W1R:6)U=&]R(#T@.
M3B=-0E!34TE344PP,2<L($!P87-S=V]R9" ]($XG)PT*1T\-"@.T*+2T@.57!D
M871I;F<@.=&AE(&%G96YT('!R;V9I;&4@.9&5F875L=',-"G-P7TU3=7!D871E
M7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE7VED(#T@.,0T*1T\-"G-P7TU3
M=7!D871E7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE7VED(#T@.,@.T*1T\-
M"G-P7TU3=7!D871E7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE7VED(#T@.
M- T*1T\-"G-P7TU3=7!D871E7V%G96YT='EP95]D969A=6QT($!P<F]F:6QE
M7VED(#T@.-@.T*1T\-"G-P7TU3=7!D871E7V%G96YT='EP95]D969A=6QT($!P
M<F]F:6QE7VED(#T@.,3$-"D=/#0H-"BTM($%D9&EN9R!T:&4@.9&ES=')I8G5T
M:6]N(&1A=&%B87-E#0IE>&5C('-P7V%D9&1I<W1R:6)U=&EO;F1B("! 9&%T
M86)A<V4@./2!.)V1I<W1R:6)U=&EO;B<L($!D871A7V9O;&1E<B ]($XG13I<
M4U%,,DM<35-344Q$051!7$U34U%,7$1A=&$G+"! 9&%T85]F:6QE(#T@.3B=D
M:7-T<FEB=71I;VXN341&)RP@.0&1A=&%?9FEL95]S:7IE(#T@.,S,S+"! ;&]G
M7V9O;&1E<B ]($XG13I<4U%,,DM<35-344Q$051!7$U34U%,7$1A=&$G+"!
M;&]G7V9I;&4@./2!.)V1I<W1R:6)U=&EO;BY,1$8G+"! ;&]G7V9I;&5?<VEZ
M92 ](#4V+"! ;6EN7V1I<W1R971E;G1I;VX@./2 P+"! ;6%X7V1I<W1R971E
M;G1I;VX@./2 W,BP@.0&AI<W1O<GE?<F5T96YT:6]N(#T@.-#@.L($!S96-U<FET
M>5]M;V1E(#T@.,0T*1T\-"@.T*+2T@.061D:6YG('1H92!D:7-T<FEB=71I;VX@.
M<'5B;&ES:&5R#0IE>&5C('-P7V%D9&1I<W1P=6)L:7-H97(@.($!P=6)L:7-H
M97(@./2!.)TU"4%-325-13# Q)RP@.0&1I<W1R:6)U=&EO;E]D8B ]($XG9&ES
M=')I8G5T:6]N)RP@.0'-E8W5R:71Y7VUO9&4@./2 Q+"! =V]R:VEN9U]D:7)E
M8W1O<GD@./2!.)UQ<34)04U-)4U%,,#%<4D503$1!5$$G+"! =')U<W1E9" ]
M($XG9F%L<V4G+"! =&AI<F1P87)T>5]F;&%G(#T@., T*1T\-"@.T*+RHJ*BHJ
M*B!%;F0Z(%-C<FEP="!T;R!B92!R=6X@.870@.1&ES=')I8G5T;W(Z($U"4%-3
M25-13# Q("HJ*BHJ*B\-"@.T*+RHJ*BHJ*B!"96=I;CH@.4V-R:7!T('1O(&)E
M(')U;B!A="!0=6)L:7-H97(Z($U"4%-325-13# Q("HJ*BHJ*B\-"BTM($%D
M9&EN9R!T:&4@.<F5G:7-T97)E9"!S=6)S8W)I8F5R#0IE>&5C('-P7V%D9'-U
M8G-C<FEB97(@.0'-U8G-C<FEB97(@./2!.)TA"4%-325-13# Q)RP@.0'1Y<&4@.
M/2 P+"! <V5C=7)I='E?;6]D92 ](#$L($!F<F5Q=65N8WE?='EP92 ](#8T
M+"! 9G)E<75E;F-Y7VEN=&5R=F%L(#T@.,2P@.0&9R97%U96YC>5]R96QA=&EV
M95]I;G1E<G9A;" ](#(L($!F<F5Q=65N8WE?<F5C=7)R96YC95]F86-T;W(@.
M/2 P+"! 9G)E<75E;F-Y7W-U8F1A>2 ](#@.L($!F<F5Q=65N8WE?<W5B9&%Y
M7VEN=&5R=F%L(#T@.,2P@.0&%C=&EV95]S=&%R=%]D871E(#T@.,"P@.0&%C=&EV
M95]E;F1?9&%T92 ](# L($!A8W1I=F5?<W1A<G1?=&EM95]O9E]D87D@./2 P
M+"! 86-T:79E7V5N9%]T:6UE7V]F7V1A>2 ](#(S-3DP,"P@.0&1E<V-R:7!T
M:6]N(#T@.3B<G#0H@.97AE8R!S<%]C:&%N9V5S=6)S8W)I8F5R7W-C:&5D=6QE
M($!S=6)S8W)I8F5R(#T@.3B=(0E!34TE344PP,2<L($!A9V5N= %]T>7!E(#T@.
M,2P@.0&%C=&EV95]E;F1?9&%T92 ](# -"D=/#0H-"BTM($5N86)L:6YG('1H
M92!R97!L:6-A=&EO;B!D871A8F%S90T*=7-E(&UA<W1E<@.T*1T\-"@.T*97AE
M8R!S<%]R97!L:6-A=&EO;F1B;W!T:6]N($!D8FYA;64@./2!.)T]21$5214Y4
M4EDG+"! ;W!T;F%M92 ]($XG<'5B;&ES:"<L($!V86QU92 ]($XG=')U92<-
M"D=/#0H-"G5S92!;3U)$15)%3E1265T-"D=/#0H-"BTM($%D9&EN9R!T:&4@.
M=')A;G-A8W1I;VYA;"!P=6)L:6-A=&EO;@.T*97AE8R!S<%]A9&1P=6)L:6-A
M=&EO;B! <'5B;&EC871I;VX@./2!.)T%S<V]C7TQO9R<L($!R97-T<FEC=&5D
M(#T@.3B=F86QS92<L($!S>6YC7VUE=&AO9" ]($XG;F%T:79E)RP@.0')E<&Q?
M9G)E<2 ]($XG8V]N=&EN=6]U<R<L($!D97-C<FEP=&EO;B ]($XG5')A;G-A
M8W1I;VYA;"!P=6)L:6-A=&EO;B!O9B!/4D1%4D5.5%)9(&1A=&%B87-E(&9R
M;VT@.4'5B;&ES:&5R($U"4%-325-13# Q+B<L($!S=&%T=7,@./2!.)V%C=&EV
M92<L($!A;&QO=U]P=7-H(#T@.3B=T<G5E)RP@.0&%L;&]W7W!U;&P@./2!.)W1R
M=64G+"! 86QL;W=?86YO;GEM;W5S(#T@.3B=F86QS92<L($!E;F%B;&5D7V 9O
M<E]I;G1E<FYE=" ]($XG9F%L<V4G+"! :6YD97!E;F1E;G1?86=E;G0@./2!.
M)W1R=64G+"! :6UM961I871E7W-Y;F,@./2!.)V9A;'-E)RP@.0&%L;&]W7W-Y
M;F-?=')A;B ]($XG=')U92<L($!A=71O9V5N7W-Y;F-?<')O8W,@./2!.)W1R
M=64G+"! <F5T96YT:6]N(#T@.,S,V+"! 86QL;W=?<75E=65D7W1R86X@./2!.
M)V9A;'-E)RP@.0'-N87!S:&]T7VEN7V1E9F%U;'1F;VQD97(@./2!.)W1R=64G
M+"! 8V]M<')E<W-?<VYA<'-H;W0@./2!.)V9A;'-E)RP@.0&9T<%]P;W)T(#T@.
M,C$L($!F='!?;&]G:6X@./2!.)V%N;VYY;6]U<R<L($!A;&QO=U]D=',@./2!.
M)V9A;'-E)RP@.0&%L;&]W7W-U8G-C<FEP=&EO;E]C;W!Y(#T@.3B=F86QS92<L
M($!A9&1?=&]?86-T:79E7V1I<F5C=&]R>2 ]($XG9F%L<V4G+"! <7)E861E
M<E]J;V)?;F%M92 ]($XG6TU"4%-325-13# Q72XV)PT*97AE8R!S<%]A9&1P
M=6)L:6-A=&EO;E]S;F%P<VAO="! <'5B;&EC871I;VX@./2!.)T%S<V]C7TQO
M9R<L0&9R97%U96YC>5]T>7!E(#T@.-"P@.0&9R97%U96YC>5]I;G1E<G9A;" ]
M(#$L($!F<F5Q=65N8WE?<F5L871I=F5?:6YT97)V86P@./2 Q+"! 9G)E<75E
M;F-Y7W)E8W5R<F5N8V5?9F%C=&]R(#T@.,"P@.0&9R97%U96YC>5]S=6)D87D@.
M/2 X+"! 9G)E<75E;F-Y7W-U8F1A>5]I;G1E<G9A;" ](#$L($!A8W1I=F5?
M<W1A<G1?9&%T92 ](# L($!A8W1I=F5?96YD7V1A=&4@./2 P+"! 86-T:79E
M7W-T87)T7W1I;65?;V9?9&%Y(#T@.,"P@.0&%C=&EV95]E;F1?=&EM95]O9E]D
M87D@./2 R,S4Y-3DL($!S;F%P<VAO=%]J;V)?;F%M92 ]($XG34)04U-)4U%,
M,#$M3U)$15)%3E1262U!<W-O8U],;V<M,34G#0I'3PT*#0IE>&5C('-P7V=R
M86YT7W!U8FQI8V%T:6]N7V%C8V5S<R! <'5B;&EC871I;VX@./2!.)T%S<V]C
M7TQO9R<L($!L;V=I;B ]($XG0E5)3%1)3EQ!9&UI;FES=')A=&]R<R<-"D=/
M#0IE>&5C('-P7V=R86YT7W!U8FQI8V%T:6]N7V%C8V5S<R! <'5B;&EC871I
M;VX@./2!.)T%S<V]C7TQO9R<L($!L;V=I;B ]($XG9&ES=')I8G5T;W)?861M
M:6XG#0I'3PT*97AE8R!S<%]G<F%N=%]P=6)L:6-A=&EO;E]A8V-E<W,@.0'!U
M8FQI8V%T:6]N(#T@.3B=!<W-O8U],;V<G+"! ;&]G:6X@./2!.)W-A)PT*1T\-
M"@.T*+2T@.061D:6YG('1H92!T<F%N<V%C=&EO;F%L(&%R=&EC; &5S#0IE>&5C
M('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T:6]N(#T@.3B=!<W-O8U],;V<G+"!
M87)T:6-L92 ]($XG;&]G7V%T=&5N9&%N8V4G+"! <V]U<F-E7V]W;F5R(#T@.
M3B=D8F\G+"! <V]U<F-E7V]B:F5C=" ]($XG;&]G7V%T=&5N9&%N8V4G+"!
M9&5S=&EN871I;VY?=&%B;&4@./2!.)VQO9U]A='1E;F1A;F-E)RP@.0'1Y<&4@.
M/2!.)VQO9V)A<V5D)RP@.0&-R96%T:6]N7W-C<FEP=" ](&YU;&PL($!D97-C
M<FEP=&EO;B ](&YU;&PL($!P<F5?8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.
M0'-C:&5M85]O<'1I;VX@./2 P># P,# P,# P,# P,$-%1C,L($!S=&%T=7,@.
M/2 Q-BP@.0'9E<G1I8V%L7W!A<G1I=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M
M9" ]($XG0T%,3"!S<%]-4VEN<U]L;V=?871T96YD86YC92<L($!D96Q?8VUD
M(#T@.3B=#04Q,('-P7TU39&5L7VQO9U]A='1E;F1A;F-E)RP@.0'5P9%]C;60@.
M/2!.)TU#04Q,('-P7TU3=7!D7VQO9U]A='1E;F1A;F-E)RP@.0&9I;'1E<B ]
M(&YU;&PL($!S>6YC7V]B:F5C=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A
M;F=E(#T@.3B=F86QS92<-"D=/#0IE>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI
M8V%T:6]N(#T@.3B=!<W-O8U],;V<G+"! 87)T:6-L92 ]($XG;&]G7V1E<&5N
M9&5N=',G+"! <V]U<F-E7V]W;F5R(#T@.3B=D8F\G+"! <V]U<F-E7V]B:F5C
M=" ]($XG;&]G7V1E<&5N9&5N=',G+"! 9&5S=&EN871I;VY?=&%B;&4@./2!.
M)VQO9U]D97!E;F1E;G1S)RP@.0'1Y<&4@./2!.)VQO9V)A<V5D)RP@.0&-R96%T
M:6]N7W-C<FEP=" ](&YU;&PL($!D97-C<FEP=&EO;B ](&YU;&PL($!P<F5?
M8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.0'-C:&5M85]O<'1I;VX@./2 P># P
M,# P,# P,# P,$-%1C,L($!S=&%T=7,@./2 Q-BP@.0'9E<G1I8V%L7W!A<G1I
M=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M9" ]($XG0T%,3"!S<%]-4VEN<U]L
M;V=?9&5P96YD96YT<R<L($!D96Q?8VUD(#T@.3B=#04Q,('-P7TU39&5L7VQO
M9U]D97!E;F1E;G1S)RP@.0'5P9%]C;60@./2!.)TU#04Q,('-P7TU3=7!D7VQO
M9U]D97!E;F1E;G1S)RP@.0&9I;'1E<B ](&YU;&PL($!S>6YC7V]B:F5C=" ]
M(&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E(#T@.3B=F86QS9 2<-"D=/#0IE
M>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T:6]N(#T@.3B=!<W-O8U],;V<G
M+"! 87)T:6-L92 ]($XG;&]G7V1E=&%I;',G+"! <V]U<F-E7V]W;F5R(#T@.
M3B=D8F\G+"! <V]U<F-E7V]B:F5C=" ]($XG;&]G7V1E=&%I;',G+"! 9&5S
M=&EN871I;VY?=&%B;&4@./2!.)VQO9U]D971A:6QS)RP@.0'1Y<&4@./2!.)VQO
M9V)A<V5D)RP@.0&-R96%T:6]N7W-C<FEP=" ](&YU;&PL($!D97-C<FEP=&EO
M;B ](&YU;&PL($!P<F5?8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.0'-C:&5M
M85]O<'1I;VX@./2 P># P,# P,# P,# P,$-%1C,L($!S=&%T=7,@./2 Q-BP@.
M0'9E<G1I8V%L7W!A<G1I=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M9" ]($XG
M0T%,3"!S<%]-4VEN<U]L;V=?9&5T86EL<R<L($!D96Q?8VUD(#T@.3B=#04Q,
M('-P7TU39&5L7VQO9U]D971A:6QS)RP@.0'5P9%]C;60@./2!.)TU#04Q,('-P
M7TU3=7!D7VQO9U]D971A:6QS)RP@.0&9I;'1E<B ](&YU;&PL($!S>6YC7V]B
M:F5C=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E(#T@.3B=F86QS92 <-
M"D=/#0IE>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T:6]N(#T@.3B=!<W-O
M8U],;V<G+"! 87)T:6-L92 ]($XG;&]G7VYO4F5H:7)E)RP@.0'-O=7)C95]O
M=VYE<B ]($XG9&)O)RP@.0'-O=7)C95]O8FIE8W0@./2!.)VQO9U]N;U)E:&ER
M92<L($!D97-T:6YA=&EO;E]T86)L92 ]($XG;&]G7VYO4F5H:7)E)RP@.0'1Y
M<&4@./2!.)VQO9V)A<V5D)RP@.0&-R96%T:6]N7W-C<FEP=" ](&YU;&PL($!D
M97-C<FEP=&EO;B ](&YU;&PL($!P<F5?8W)E871I;VY?8VUD(#T@.3B=D<F]P
M)RP@.0'-C:&5M85]O<'1I;VX@./2 P># P,# P,# P,# P,$-%1C,L($!S=&%T
M=7,@./2 Q-BP@.0'9E<G1I8V%L7W!A<G1I=&EO;B ]($XG9F%L<V4G+"! :6YS
M7V-M9" ]($XG0T%,3"!S<%]-4VEN<U]L;V=?;F]296AI<F4G+"! 9&5L7V-M
M9" ]($XG0T%,3"!S<%]-4V1E;%]L;V=?;F]296AI<F4G+"! =7!D7V-M9" ]
M($XG34-!3$P@.<W!?35-U<&1?;&]G7VYO4F5H:7)E)RP@.0&9I;'1E<B ](&YU
M;&PL($!S>6YC7V]B:F5C=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E
M(#T@.3B=F86QS92<-"D=/#0IE>&5C('-P7V%D9&%R=&EC;&4@.0'!U8FQI8V%T
M:6]N(#T@.3B=!<W-O8U],;V<G+"! 87)T:6-L92 ]($XG;&]G7W-E<&%R871I
M;VYS)RP@.0'-O=7)C95]O=VYE<B ]($XG9&)O)RP@.0'-O=7)C95]O8FIE8W0@.
M/2!.)VQO9U]S97!A<F%T:6]N<R<L($!D97-T:6YA=&EO;E]T86)L92 ]($XG
M;&]G7W-E<&%R871I;VYS)RP@.0'1Y<&4@./2!.)VQO9V)A<V5D)RP@.0&-R96%T
M:6]N7W-C<FEP=" ](&YU;&PL($!D97-C<FEP=&EO;B ](&YU;&PL($!P<F5?
M8W)E871I;VY?8VUD(#T@.3B=D<F]P)RP@.0'-C:&5M85]O<'1I;VX@./2 P># P
M,# P,# P,# P,$-%1C,L($!S=&%T=7,@./2 Q-BP@.0'9E<G1I8V%L7W!A<G1I
M=&EO;B ]($XG9F%L<V4G+"! :6YS7V-M9" ]($XG0T%,3"!S<%]-4VEN<U]L
M;V=?<V5P87)A=&EO;G,G+"! 9&5L7V-M9" ]($XG0T%,3"!S<%]-4V1E;%]L
M;V=?<V5P87)A=&EO;G,G+"! =7!D7V-M9" ]($XG34-!3$P@.<W!?35-U<&1?
M;&]G7W-E<&%R871I;VYS)RP@.0&9I;'1E<B ](&YU;&PL($!S>6YC7V]B:F5C
M=" ](&YU;&PL($!A=71O7VED96YT:71Y7W)A;F=E(#T@.3B=F86QS92 <-"D=/
M#0H-"BTM($%D9&EN9R!T:&4@.=')A;G-A8W1I;VYA;"!S=6)S8W)I<'1I;VX-
M"F5X96,@.<W!?861D<W5B<V-R:7!T:6]N($!P=6)L:6-A=&EO;B ]($XG07-S
M;V-?3&]G)RP@.0&%R=&EC;&4@./2!.)V%L;"<L($!S=6)S8W)I8F5R(#T@.3B=(
M0E!34TE344PP,2<L($!D97-T:6YA=&EO;E]D8B ]($XG3U)$15)%3E1262<L
M($!S>6YC7W1Y<&4@./2!.)V%U=&]M871I8R<L($!U<&1A=&5?;6]D92 ]($XG
M<WEN8R!T<F%N)RP@.0&]F9FQO861A9V5N=" ](# L($!D='-?<&%C:V%G95]L
<;V-A=&EO;B ]($XG9&ES=')I8G5T;W(G#0I'3P``
`
end
|||You are using Updateable Subscriptions with Queued Updating.
the log reader agent name should (but doesn't have to be blank). Any value
you put in here will be ignored.
I just want to confirm that the log reader agent is not showing up in the
publisher. It should not show up on the subscriber.
Also can you check your jobs folder (under the management folder) to see if
a log reader agent/job is not created for this job,
Sometimes the agents will not show up in the replication agents folder but
are actually there, and you can only see them in the jobs folder. When this
happens it is difficult to administer your agents, but you will have a
working solution.
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:%238DZmWDqEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Thank's for the response,
> Attached is the replication script with the beginning part that sets up
> the
> distributor and the setup for one of the publications that is failing to
> create a Log Reader agent.
> As you can see the logreader_job_name parameter is absent. Also I checked
> in
> the server and the logread.exe executable is there in its proper place.
> The server is running Sql Server 2000 Standard SP3 on Microsoft Windows
> Server 2003 Standard, replicating to a server with the same configuraton
> (except 2500 miles away).
> This same replication worked just fine on previous servers that the
> application was migrated from: Sql Server 7.0 on Windows 2000 Server
> Advanced (clustered).
> Jim
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ec33qHCqEHA.3464@.TK2MSFTNGP14.phx.gbl...
> don't
> logreader
> use
>
>
|||It was setup for Immediate Updating and Queued Updating as a failover. Even
still, doesn't there need to be a Log Reader agent to pickup any
transactions to replicate?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZ48JFJqEHA.3244@.tk2msftngp13.phx.gbl...
> You are using Updateable Subscriptions with Queued Updating.
> the log reader agent name should (but doesn't have to be blank). Any value
> you put in here will be ignored.
> I just want to confirm that the log reader agent is not showing up in the
> publisher. It should not show up on the subscriber.
> Also can you check your jobs folder (under the management folder) to see
if
> a log reader agent/job is not created for this job,
> Sometimes the agents will not show up in the replication agents folder but
> are actually there, and you can only see them in the jobs folder. When
this[vbcol=seagreen]
> happens it is difficult to administer your agents, but you will have a
> working solution.
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%238DZmWDqEHA.3668@.TK2MSFTNGP15.phx.gbl...
checked[vbcol=seagreen]
you[vbcol=seagreen]
errors.
>
|||The log reader agent is not showing up at the publisher or the subscriber.
There is also no agent job listed.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZ48JFJqEHA.3244@.tk2msftngp13.phx.gbl...
> You are using Updateable Subscriptions with Queued Updating.
> the log reader agent name should (but doesn't have to be blank). Any value
> you put in here will be ignored.
> I just want to confirm that the log reader agent is not showing up in the
> publisher. It should not show up on the subscriber.
> Also can you check your jobs folder (under the management folder) to see
if
> a log reader agent/job is not created for this job,
> Sometimes the agents will not show up in the replication agents folder but
> are actually there, and you can only see them in the jobs folder. When
this[vbcol=seagreen]
> happens it is difficult to administer your agents, but you will have a
> working solution.
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%238DZmWDqEHA.3668@.TK2MSFTNGP15.phx.gbl...
checked[vbcol=seagreen]
you[vbcol=seagreen]
errors.
>
|||yes, there will be a log reader agent on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:%23oQdcIKqEHA.1576@.TK2MSFTNGP12.phx.gbl...
> It was setup for Immediate Updating and Queued Updating as a failover.
Even[vbcol=seagreen]
> still, doesn't there need to be a Log Reader agent to pickup any
> transactions to replicate?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZ48JFJqEHA.3244@.tk2msftngp13.phx.gbl...
value[vbcol=seagreen]
the[vbcol=seagreen]
> if
but[vbcol=seagreen]
> this
up[vbcol=seagreen]
to[vbcol=seagreen]
> checked
place.[vbcol=seagreen]
Windows[vbcol=seagreen]
configuraton[vbcol=seagreen]
agents[vbcol=seagreen]
create[vbcol=seagreen]
> you
transactional[vbcol=seagreen]
are[vbcol=seagreen]
> errors.
called[vbcol=seagreen]
the[vbcol=seagreen]
is
>
|||I tried your script. It works on my machine. I think there is something
wrong with your machine. Can you try to disable replication, recreate it,
and then recreate your publication and subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uFdQI6KqEHA.3712@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> yes, there will be a log reader agent on the publisher.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23oQdcIKqEHA.1576@.TK2MSFTNGP12.phx.gbl...
> Even
> value
> the
see[vbcol=seagreen]
> but
[vbcol=seagreen]
> up
failing[vbcol=seagreen]
> to
> place.
> Windows
> configuraton
> agents
> create
sure[vbcol=seagreen]
> transactional
> are
no
> called
> the
> is
>
|||That's what I feared. There is definitely something wrong with the way
replication was installed on this box. Unfortunately I was not the one who
installed Sql Server and replication. I was called in after the fact when it
looked like replication was not working. It may be an issue of permissions
and rights during the setup of replication on the server, as this company
does not want to give the Sql Server DBAs admin rights to their servers.
Thanks for all your help!
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ejcXuBOqEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I tried your script. It works on my machine. I think there is something
> wrong with your machine. Can you try to disable replication, recreate it,
> and then recreate your publication and subscription.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uFdQI6KqEHA.3712@.TK2MSFTNGP15.phx.gbl...
in[vbcol=seagreen]
> see
folder[vbcol=seagreen]
When[vbcol=seagreen]
a[vbcol=seagreen]
sets[vbcol=seagreen]
> failing
the[vbcol=seagreen]
Server[vbcol=seagreen]
> sure
shots[vbcol=seagreen]
> no
installing[vbcol=seagreen]
agent
>
|||I don't think it is a problem with the way the previous dba installed
replication, rather there is something wrong with the SQL Server
environment; ie missing stored procedures, or an inconsistent application of
SP 3. I suggest you reapply SP 3 and try again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uiosaiOqEHA.2588@.TK2MSFTNGP12.phx.gbl...
> That's what I feared. There is definitely something wrong with the way
> replication was installed on this box. Unfortunately I was not the one who
> installed Sql Server and replication. I was called in after the fact when
it[vbcol=seagreen]
> looked like replication was not working. It may be an issue of permissions
> and rights during the setup of replication on the server, as this company
> does not want to give the Sql Server DBAs admin rights to their servers.
> Thanks for all your help!
> Jim
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ejcXuBOqEHA.3244@.tk2msftngp13.phx.gbl...
it,[vbcol=seagreen]
failover.[vbcol=seagreen]
Any[vbcol=seagreen]
> in
to[vbcol=seagreen]
> folder
> When
have[vbcol=seagreen]
> a
> sets
I[vbcol=seagreen]
> the
> Server
any[vbcol=seagreen]
> shots
but[vbcol=seagreen]
no[vbcol=seagreen]
being
> installing
> agent
>
Monday, March 19, 2012
Logon to DTS from a different domain
I have a series of DTS's that need to be executed in a certain order and therefore have created a VB app that would execute the DTS's and perform a few administrative funtions for the user if successful.
The DBA for the SQL Server has given me a certain Windows Logon and I must logon via Terminal Server when I need to gain access to the SQL Server Manager. This is where I have created the DTS's - all created and owned by that user logon with certain rights. My VB application will be run from a different user machine on a different domain and therefore has a different logon- normal Windows logon. This user does not have access to the SQL Server and the DTS's need to logon with the same logon as under which it was created.
BUT...
LoadFromSQLServer offers only a SQL or Trusted connection by which one can let the user connect. By using the Windows connection flag, the LoadFromSQLServer function now ignores the logon username & password and posts the normal Windows logon. Obviously, the logon now fails for the user.
How can I let the LoadFromSQLServer funtion logon to the DTS Package with a different domain, username & password than the local Windows Logon information ?
GrahamAnybody out there ?
The DBA for the SQL Server has given me a certain Windows Logon and I must logon via Terminal Server when I need to gain access to the SQL Server Manager. This is where I have created the DTS's - all created and owned by that user logon with certain rights. My VB application will be run from a different user machine on a different domain and therefore has a different logon- normal Windows logon. This user does not have access to the SQL Server and the DTS's need to logon with the same logon as under which it was created.
BUT...
LoadFromSQLServer offers only a SQL or Trusted connection by which one can let the user connect. By using the Windows connection flag, the LoadFromSQLServer function now ignores the logon username & password and posts the normal Windows logon. Obviously, the logon now fails for the user.
How can I let the LoadFromSQLServer funtion logon to the DTS Package with a different domain, username & password than the local Windows Logon information ?
GrahamAnybody out there ?
Logon Failure when executing report
I'm using RS 2005. I created a report that works fine in Visual Studio
and I can deploy it to the report server without any errors. But when
I try to execute the report in the Report Manager or by using a URL, I
get the following error: Logon Failed: unknown user name or bad
password. I have the default security settings for the report (Builtin
Administrators Group only). I am logged on as a domain administrator
when I try to run the report. What logon is the error referring to?
Thanks in advance for any help.How do you authenticate against your datasource?
What happens if you try it from a different computer?
If you are using NT authentication in your datasource, check what user you
are running the report as. One way is by adding your report site to Trusted
SItes in IE (Internet Options -Security) and then set Always prompt for
username and password in the Security Settings.
Kaisa M. Lindahl
"dba56" <lvigorito@.berdonllp.com> wrote in message
news:1137038010.522621.70160@.o13g2000cwo.googlegroups.com...
> I'm using RS 2005. I created a report that works fine in Visual Studio
> and I can deploy it to the report server without any errors. But when
> I try to execute the report in the Report Manager or by using a URL, I
> get the following error: Logon Failed: unknown user name or bad
> password. I have the default security settings for the report (Builtin
> Administrators Group only). I am logged on as a domain administrator
> when I try to run the report. What logon is the error referring to?
> Thanks in advance for any help.
>|||Thanks for the quick response. It turned out to be the execution
account. Don't know why it needs to log on with the execution account
when executing a report. But once I entered the correct login and
password for the execution account in the configuration utility,
reports run without any glitches.|||Hi
I've the same problem when executing my report from the Report Manager. It
works fine in the development tool though.
I run my report under almost the same conditions as you did, i.e
- as a local admin on the test server (Report Service and source database on
the same machine, the development tool to)
- The data source is set the SQL authentication and I have gone as far as
using the sa login
- In the config manager I have updated the Sevice to run as a local user
which is a member of the admin group and the two ReportinServices groups.
I still get the "log in failure". What more is there to do?
/Jesper
"dba56" wrote:
> Thanks for the quick response. It turned out to be the execution
> account. Don't know why it needs to log on with the execution account
> when executing a report. But once I entered the correct login and
> password for the execution account in the configuration utility,
> reports run without any glitches.
>
and I can deploy it to the report server without any errors. But when
I try to execute the report in the Report Manager or by using a URL, I
get the following error: Logon Failed: unknown user name or bad
password. I have the default security settings for the report (Builtin
Administrators Group only). I am logged on as a domain administrator
when I try to run the report. What logon is the error referring to?
Thanks in advance for any help.How do you authenticate against your datasource?
What happens if you try it from a different computer?
If you are using NT authentication in your datasource, check what user you
are running the report as. One way is by adding your report site to Trusted
SItes in IE (Internet Options -Security) and then set Always prompt for
username and password in the Security Settings.
Kaisa M. Lindahl
"dba56" <lvigorito@.berdonllp.com> wrote in message
news:1137038010.522621.70160@.o13g2000cwo.googlegroups.com...
> I'm using RS 2005. I created a report that works fine in Visual Studio
> and I can deploy it to the report server without any errors. But when
> I try to execute the report in the Report Manager or by using a URL, I
> get the following error: Logon Failed: unknown user name or bad
> password. I have the default security settings for the report (Builtin
> Administrators Group only). I am logged on as a domain administrator
> when I try to run the report. What logon is the error referring to?
> Thanks in advance for any help.
>|||Thanks for the quick response. It turned out to be the execution
account. Don't know why it needs to log on with the execution account
when executing a report. But once I entered the correct login and
password for the execution account in the configuration utility,
reports run without any glitches.|||Hi
I've the same problem when executing my report from the Report Manager. It
works fine in the development tool though.
I run my report under almost the same conditions as you did, i.e
- as a local admin on the test server (Report Service and source database on
the same machine, the development tool to)
- The data source is set the SQL authentication and I have gone as far as
using the sa login
- In the config manager I have updated the Sevice to run as a local user
which is a member of the admin group and the two ReportinServices groups.
I still get the "log in failure". What more is there to do?
/Jesper
"dba56" wrote:
> Thanks for the quick response. It turned out to be the execution
> account. Don't know why it needs to log on with the execution account
> when executing a report. But once I entered the correct login and
> password for the execution account in the configuration utility,
> reports run without any glitches.
>
Logon failure : (rsInternalError)
On a SQL server 2000 SP4 on XP PRO SP2, I have installed Reporting Services.
I have created an local administrator user and I have given this local
account as "Domain user Account" during installation.
When I try to acces report server, it fails with the message "Logon failure:
unknown user name or bad password. "
In the logfile of Reporting services, I read these informations :
aspnet_wp!library!690!02/03/2006-16:24:09:: i INFO: Call to GetPermissions:/
aspnet_wp!crypto!690!02/03/2006-16:24:09:: i INFO: Initializing crypto as
user: U000103096\ASPNET
aspnet_wp!crypto!690!02/03/2006-16:24:09:: i INFO: Exporting public key
aspnet_wp!library!690!02/03/2006-16:24:10:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
Une erreur interne s'est produite sur le serveur de rapports. Pour plus
d'informations, consultez le journal des erreurs., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
Une erreur interne s'est produite sur le serveur de rapports. Pour plus
d'informations, consultez le journal des erreurs. -->
System.Runtime.InteropServices.COMException (0x8007052E): Logon failure:
unknown user name or bad password.
Can somebody help me to solve this problem ? Thanks for allHi Gaby,
Welcome to the MSDN newsgroup.
From your description, I understand you have installed SQL Server 2000(with
reporting service) on a Windows XP SP2 machine. Also, during the
installation time, you configured the Reporting Service to use a certain
domain account which has been granted the "Local Administrator" role.
However, when you try accessing the report server application, it threw the
following exception:
"Logon failure: unknown user name or bad password. " ,correct?
Based on the log file content you provided, the error occured when the
report server try performing some security operations like reading account
info. I've also checked the error and find some reference in MSDN on the
similiar problem. Are you try viewing a report which use external
datasource and use prompted or stored Windows credentials to connect to the
external datasource? If so, you can have a look at the following
troubleshooting article, and the below section
"Connection error, where login failed for <servername>\ASPNET"
is describing the similiar problem as yours.
#Troubleshooting Server and Database Problems
http://msdn.microsoft.com/library/en-us/rstshoot/htm/trs_tshootdev_v1_3dcz.a
sp?frame=true
Please feel free to post here if there's anything else we can help.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Steven
Thank you for your help but I am sorry to say that your recommendations
didn't work on my PC
I have just installed an SQL Server on my windows XP (SQL server 2000 + SP4)
and the Reporting Services (+ service pack 2). After that, before making any
own report, I have just tried to contact the report server, on this PC, in
an IE5 session with the address : localhost\reports. And then I have received
the error message : logon failed !!
My PC is an "entreprise PC", this a lot of components "masterised" in a
"logically gathered software" and I think that perhaps some authorizations
are not correctly installed for my requirements, but I don't know where I
have to search !!
"Steven Cheng[MSFT]" wrote:
> Hi Gaby,
> Welcome to the MSDN newsgroup.
> From your description, I understand you have installed SQL Server 2000(with
> reporting service) on a Windows XP SP2 machine. Also, during the
> installation time, you configured the Reporting Service to use a certain
> domain account which has been granted the "Local Administrator" role.
> However, when you try accessing the report server application, it threw the
> following exception:
> "Logon failure: unknown user name or bad password. " ,correct?
> Based on the log file content you provided, the error occured when the
> report server try performing some security operations like reading account
> info. I've also checked the error and find some reference in MSDN on the
> similiar problem. Are you try viewing a report which use external
> datasource and use prompted or stored Windows credentials to connect to the
> external datasource? If so, you can have a look at the following
> troubleshooting article, and the below section
> "Connection error, where login failed for <servername>\ASPNET"
> is describing the similiar problem as yours.
> #Troubleshooting Server and Database Problems
> http://msdn.microsoft.com/library/en-us/rstshoot/htm/trs_tshootdev_v1_3dcz.a
> sp?frame=true
> Please feel free to post here if there's anything else we can help.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>
>
>|||Thanks for your response Gaby,
So the problem is somewhat different from that mentioned in the doc. Since
the error stack shows there is some login fail error and also mentioend the
MACHINE\ASPNET account, I think you can try the followings to see whether
it is concerned with account permission:
1. Change the reporting service windows service's identity from the current
account( the one you specified during setup) to Local System.
2. Temporarlily Add the ASP.NET process account (machine\ASPNET) into
local administrators group or just replace the ASP.NET process account
from "Machine" to "SYSTEM", this can be set in the .net framework's
machine.config(under C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG)
After that, restart reporting service to see whether it work.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thank you for your help. At the end, it works. I have just modified the
machine.config file of the current version of the framework (in the CONFIG
Folder), and change, in the processModel parameter, the value of the userName
parameter, from "machine" to "SYSTEM".
By stopping and restarting only the reporting service and the IIS server,
this modification don't work but after the "total reboot" of my computer, all
is OK and I can now see the contents of the folder in the Reports Server
Thank you for all (and I hope that our discussion can help other persons)
"Steven Cheng[MSFT]" wrote:
> Thanks for your response Gaby,
> So the problem is somewhat different from that mentioned in the doc. Since
> the error stack shows there is some login fail error and also mentioend the
> MACHINE\ASPNET account, I think you can try the followings to see whether
> it is concerned with account permission:
> 1. Change the reporting service windows service's identity from the current
> account( the one you specified during setup) to Local System.
> 2. Temporarlily Add the ASP.NET process account (machine\ASPNET) into
> local administrators group or just replace the ASP.NET process account
> from "Machine" to "SYSTEM", this can be set in the .net framework's
> machine.config(under C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG)
> After that, restart reporting service to see whether it work.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||You're welcome Gaby,
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
I have created an local administrator user and I have given this local
account as "Domain user Account" during installation.
When I try to acces report server, it fails with the message "Logon failure:
unknown user name or bad password. "
In the logfile of Reporting services, I read these informations :
aspnet_wp!library!690!02/03/2006-16:24:09:: i INFO: Call to GetPermissions:/
aspnet_wp!crypto!690!02/03/2006-16:24:09:: i INFO: Initializing crypto as
user: U000103096\ASPNET
aspnet_wp!crypto!690!02/03/2006-16:24:09:: i INFO: Exporting public key
aspnet_wp!library!690!02/03/2006-16:24:10:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
Une erreur interne s'est produite sur le serveur de rapports. Pour plus
d'informations, consultez le journal des erreurs., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
Une erreur interne s'est produite sur le serveur de rapports. Pour plus
d'informations, consultez le journal des erreurs. -->
System.Runtime.InteropServices.COMException (0x8007052E): Logon failure:
unknown user name or bad password.
Can somebody help me to solve this problem ? Thanks for allHi Gaby,
Welcome to the MSDN newsgroup.
From your description, I understand you have installed SQL Server 2000(with
reporting service) on a Windows XP SP2 machine. Also, during the
installation time, you configured the Reporting Service to use a certain
domain account which has been granted the "Local Administrator" role.
However, when you try accessing the report server application, it threw the
following exception:
"Logon failure: unknown user name or bad password. " ,correct?
Based on the log file content you provided, the error occured when the
report server try performing some security operations like reading account
info. I've also checked the error and find some reference in MSDN on the
similiar problem. Are you try viewing a report which use external
datasource and use prompted or stored Windows credentials to connect to the
external datasource? If so, you can have a look at the following
troubleshooting article, and the below section
"Connection error, where login failed for <servername>\ASPNET"
is describing the similiar problem as yours.
#Troubleshooting Server and Database Problems
http://msdn.microsoft.com/library/en-us/rstshoot/htm/trs_tshootdev_v1_3dcz.a
sp?frame=true
Please feel free to post here if there's anything else we can help.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Steven
Thank you for your help but I am sorry to say that your recommendations
didn't work on my PC
I have just installed an SQL Server on my windows XP (SQL server 2000 + SP4)
and the Reporting Services (+ service pack 2). After that, before making any
own report, I have just tried to contact the report server, on this PC, in
an IE5 session with the address : localhost\reports. And then I have received
the error message : logon failed !!
My PC is an "entreprise PC", this a lot of components "masterised" in a
"logically gathered software" and I think that perhaps some authorizations
are not correctly installed for my requirements, but I don't know where I
have to search !!
"Steven Cheng[MSFT]" wrote:
> Hi Gaby,
> Welcome to the MSDN newsgroup.
> From your description, I understand you have installed SQL Server 2000(with
> reporting service) on a Windows XP SP2 machine. Also, during the
> installation time, you configured the Reporting Service to use a certain
> domain account which has been granted the "Local Administrator" role.
> However, when you try accessing the report server application, it threw the
> following exception:
> "Logon failure: unknown user name or bad password. " ,correct?
> Based on the log file content you provided, the error occured when the
> report server try performing some security operations like reading account
> info. I've also checked the error and find some reference in MSDN on the
> similiar problem. Are you try viewing a report which use external
> datasource and use prompted or stored Windows credentials to connect to the
> external datasource? If so, you can have a look at the following
> troubleshooting article, and the below section
> "Connection error, where login failed for <servername>\ASPNET"
> is describing the similiar problem as yours.
> #Troubleshooting Server and Database Problems
> http://msdn.microsoft.com/library/en-us/rstshoot/htm/trs_tshootdev_v1_3dcz.a
> sp?frame=true
> Please feel free to post here if there's anything else we can help.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>
>
>|||Thanks for your response Gaby,
So the problem is somewhat different from that mentioned in the doc. Since
the error stack shows there is some login fail error and also mentioend the
MACHINE\ASPNET account, I think you can try the followings to see whether
it is concerned with account permission:
1. Change the reporting service windows service's identity from the current
account( the one you specified during setup) to Local System.
2. Temporarlily Add the ASP.NET process account (machine\ASPNET) into
local administrators group or just replace the ASP.NET process account
from "Machine" to "SYSTEM", this can be set in the .net framework's
machine.config(under C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG)
After that, restart reporting service to see whether it work.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thank you for your help. At the end, it works. I have just modified the
machine.config file of the current version of the framework (in the CONFIG
Folder), and change, in the processModel parameter, the value of the userName
parameter, from "machine" to "SYSTEM".
By stopping and restarting only the reporting service and the IIS server,
this modification don't work but after the "total reboot" of my computer, all
is OK and I can now see the contents of the folder in the Reports Server
Thank you for all (and I hope that our discussion can help other persons)
"Steven Cheng[MSFT]" wrote:
> Thanks for your response Gaby,
> So the problem is somewhat different from that mentioned in the doc. Since
> the error stack shows there is some login fail error and also mentioend the
> MACHINE\ASPNET account, I think you can try the followings to see whether
> it is concerned with account permission:
> 1. Change the reporting service windows service's identity from the current
> account( the one you specified during setup) to Local System.
> 2. Temporarlily Add the ASP.NET process account (machine\ASPNET) into
> local administrators group or just replace the ASP.NET process account
> from "Machine" to "SYSTEM", this can be set in the .net framework's
> machine.config(under C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG)
> After that, restart reporting service to see whether it work.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||You're welcome Gaby,
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
logon failed
hi
i am using crystal reports9 in vb6 with access database.
i Created one report called rep_customer
and wroted code on button_click event to call report from vb
vbcode---------------
public sub cust_report_button_click
connect
Set r = New ADODB.Recordset
ReportNameVar = App.Path & "\reports\rep_customers.rpt"
r.Open "select * from cust ", c, adOpenDynamic, adLockOptimistic
Set report = crxApplication.OpenReport(ReportNameVar, 1)
report.Database.SetDataSource r
CRViewer91.ReportSource = report
Rep_form.ViewReport
Rep_form.Show
End Sub
------------------
it;s working fine
i created one more report Called Purchase_order based
on two tables (purchase_order,purchase_order_details)
and wroted code on button_click event to call report from vb
vbcode---------------
public sub Purchase_report_button_click
connect
Set r = New ADODB.Recordset
ReportNameVar = App.Path & "\reports\rep_purchase.rpt"
r.Open "select Purchase_order.pono,purchase_order.date,
Purchase_order_details.Product_code ,Purchase_order_details.product_description
from purchase_order,purchase_order_details
where purchase_order.pono=purchase_order_details.pono ", c, adOpenDynamic, adLockOptimistic
Set report = crxApplication.OpenReport(ReportNameVar, 1)
report.Database.SetDataSource r
CRViewer91.ReportSource = report
Rep_form.ViewReport
Rep_form.Show
End Sub
---------------------
i am getting error like
logon failed
Details:Ado Error Code 0x80040e4d
Microsoft Jet Database Engine
Description not a valid password
sql stated 3031
Report is working fine in Desiging and Preview of Crystal Designer Environment
i am sure no problem in database connection username and password
because i am using same connection for customer report.
i hope Only the Problem while Calling from vb6 when report having 2 tables
Where is the Problem ?how Can i solve this One
Can Any one Help meOpen the report and Do verify Database
i am using crystal reports9 in vb6 with access database.
i Created one report called rep_customer
and wroted code on button_click event to call report from vb
vbcode---------------
public sub cust_report_button_click
connect
Set r = New ADODB.Recordset
ReportNameVar = App.Path & "\reports\rep_customers.rpt"
r.Open "select * from cust ", c, adOpenDynamic, adLockOptimistic
Set report = crxApplication.OpenReport(ReportNameVar, 1)
report.Database.SetDataSource r
CRViewer91.ReportSource = report
Rep_form.ViewReport
Rep_form.Show
End Sub
------------------
it;s working fine
i created one more report Called Purchase_order based
on two tables (purchase_order,purchase_order_details)
and wroted code on button_click event to call report from vb
vbcode---------------
public sub Purchase_report_button_click
connect
Set r = New ADODB.Recordset
ReportNameVar = App.Path & "\reports\rep_purchase.rpt"
r.Open "select Purchase_order.pono,purchase_order.date,
Purchase_order_details.Product_code ,Purchase_order_details.product_description
from purchase_order,purchase_order_details
where purchase_order.pono=purchase_order_details.pono ", c, adOpenDynamic, adLockOptimistic
Set report = crxApplication.OpenReport(ReportNameVar, 1)
report.Database.SetDataSource r
CRViewer91.ReportSource = report
Rep_form.ViewReport
Rep_form.Show
End Sub
---------------------
i am getting error like
logon failed
Details:Ado Error Code 0x80040e4d
Microsoft Jet Database Engine
Description not a valid password
sql stated 3031
Report is working fine in Desiging and Preview of Crystal Designer Environment
i am sure no problem in database connection username and password
because i am using same connection for customer report.
i hope Only the Problem while Calling from vb6 when report having 2 tables
Where is the Problem ?how Can i solve this One
Can Any one Help meOpen the report and Do verify Database
Monday, March 12, 2012
Logins created by default
Besides BUILTIN\Administrators and sa, are there any logins which will be
created by default in SQL Server 2000? It seems that a Windows User (the
user who installs the SQL Server) will be also created automatically.
Does using Windows Authentication or mixed mode affect which logins will be
created automatically?
Thanks."Peter" <Peter@.discussions.microsoft.com> wrote in message
news:C1A65A74-296D-4681-921F-71399767BB6B@.microsoft.com...
> Besides BUILTIN\Administrators and sa, are there any logins which will be
> created by default in SQL Server 2000? It seems that a Windows User (the
> user who installs the SQL Server) will be also created automatically.
Those are the default accounts...
> Does using Windows Authentication or mixed mode affect which logins will
be
> created automatically?
No.
Steve|||Steve,
I understand the sa and BUILTIN\Administrators are default accounts for both
Windows Authentication and Mixed mode.
But how about the Windows User login? It seems that it is created
automatically and it is the Windows User who installs the SQL Server. Is my
understanding correct?
"Steve Thompson" wrote:
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:C1A65A74-296D-4681-921F-71399767BB6B@.microsoft.com...
> Those are the default accounts...
>
> be
> No.
> Steve
>
>|||Not unless that user is a member of the local Administrators Group.
So, any local admin has Windows Auth access to the server by default. It
doesn't matter who ran setup per se.
Local admin privleges are required to install services.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
I'm trying to find out the following:
1. Whether that Windows User login(the one installed the SQL Server) is
always created automatically during installing a SQL Server using Windows
Authentication or not.
Based on my testing in installing SQL Server with Windows Authentication
when I logged onto the domain, the following logins are created automaticall
y:
sa
BUILTIN\Administrators
domain\user - the Windows User who logged onto the domain
2. Can a user who has local administrative permissions log on to the
operating system locally (so he/she is not logged onto the domain) and
install a SQL Server using Windows Authentication? If yes, since the user
only logs on locally, I guess that only sa and BUILTIN\Administrators logins
are created automatically. Am I right?
So, basically I want to know what logins are created automatically in
installing a SQL Server using Windows Authentication when:
1. the user is logged on locally
2. the user is logged onto the domain
Thanks.
"Kevin McDonnell [MSFT]" wrote:
> Not unless that user is a member of the local Administrators Group.
> So, any local admin has Windows Auth access to the server by default. It
> doesn't matter who ran setup per se.
> Local admin privleges are required to install services.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||I think the confusion may be in that the account specified to start the
services is added to the logins.
If you specify in setup that a local windows account or domain account
should be used to start the services, (MSSQL and SQL Agent)
then this account is added to the logins.
If you selected to start the service with 'localsystem' which I don't
recommend, then only the Builtin\admin and sa account is
added to the logins.
Hope this helps.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
Yes, I use a domain account to start MSSQL and SQL Agent. I don't remember
seeing any information about this account will be added to logins.
Thanks,
Peter
"Kevin McDonnell [MSFT]" wrote:
> I think the confusion may be in that the account specified to start the
> services is added to the logins.
> If you specify in setup that a local windows account or domain account
> should be used to start the services, (MSSQL and SQL Agent)
> then this account is added to the logins.
> If you selected to start the service with 'localsystem' which I don't
> recommend, then only the Builtin\admin and sa account is
> added to the logins.
> Hope this helps.
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
created by default in SQL Server 2000? It seems that a Windows User (the
user who installs the SQL Server) will be also created automatically.
Does using Windows Authentication or mixed mode affect which logins will be
created automatically?
Thanks."Peter" <Peter@.discussions.microsoft.com> wrote in message
news:C1A65A74-296D-4681-921F-71399767BB6B@.microsoft.com...
> Besides BUILTIN\Administrators and sa, are there any logins which will be
> created by default in SQL Server 2000? It seems that a Windows User (the
> user who installs the SQL Server) will be also created automatically.
Those are the default accounts...
> Does using Windows Authentication or mixed mode affect which logins will
be
> created automatically?
No.
Steve|||Steve,
I understand the sa and BUILTIN\Administrators are default accounts for both
Windows Authentication and Mixed mode.
But how about the Windows User login? It seems that it is created
automatically and it is the Windows User who installs the SQL Server. Is my
understanding correct?
"Steve Thompson" wrote:
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:C1A65A74-296D-4681-921F-71399767BB6B@.microsoft.com...
> Those are the default accounts...
>
> be
> No.
> Steve
>
>|||Not unless that user is a member of the local Administrators Group.
So, any local admin has Windows Auth access to the server by default. It
doesn't matter who ran setup per se.
Local admin privleges are required to install services.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
I'm trying to find out the following:
1. Whether that Windows User login(the one installed the SQL Server) is
always created automatically during installing a SQL Server using Windows
Authentication or not.
Based on my testing in installing SQL Server with Windows Authentication
when I logged onto the domain, the following logins are created automaticall
y:
sa
BUILTIN\Administrators
domain\user - the Windows User who logged onto the domain
2. Can a user who has local administrative permissions log on to the
operating system locally (so he/she is not logged onto the domain) and
install a SQL Server using Windows Authentication? If yes, since the user
only logs on locally, I guess that only sa and BUILTIN\Administrators logins
are created automatically. Am I right?
So, basically I want to know what logins are created automatically in
installing a SQL Server using Windows Authentication when:
1. the user is logged on locally
2. the user is logged onto the domain
Thanks.
"Kevin McDonnell [MSFT]" wrote:
> Not unless that user is a member of the local Administrators Group.
> So, any local admin has Windows Auth access to the server by default. It
> doesn't matter who ran setup per se.
> Local admin privleges are required to install services.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||I think the confusion may be in that the account specified to start the
services is added to the logins.
If you specify in setup that a local windows account or domain account
should be used to start the services, (MSSQL and SQL Agent)
then this account is added to the logins.
If you selected to start the service with 'localsystem' which I don't
recommend, then only the Builtin\admin and sa account is
added to the logins.
Hope this helps.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
Yes, I use a domain account to start MSSQL and SQL Agent. I don't remember
seeing any information about this account will be added to logins.
Thanks,
Peter
"Kevin McDonnell [MSFT]" wrote:
> I think the confusion may be in that the account specified to start the
> services is added to the logins.
> If you specify in setup that a local windows account or domain account
> should be used to start the services, (MSSQL and SQL Agent)
> then this account is added to the logins.
> If you selected to start the service with 'localsystem' which I don't
> recommend, then only the Builtin\admin and sa account is
> added to the logins.
> Hope this helps.
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Wednesday, March 7, 2012
Login vs. User (basic questions)
In the Books Online, it is stated that '...a single login is mapped to one u
ser account created in each database the login is accessing'. As long as on
ly one login can be used in a db and a user is associated with a login, what
is the purpose of having a
distinction between a User and a Login? Why can't you just add a Login to a
database and skip the User? Also, what is the purpose of allowing the user
name to be different than the login name (I realize it defaults to the login
name)? Since connection
strings use the login name, what purpose is there to have the username poten
tially different?
I'm assuming this has something to do with database (as opposed to server) r
oles, in which case multiple users can be added to a role. However, why are
n't Logins added directly to roles instead of Users?Sorry, but you should try and keep this as simple as possible.
A login is a security mechanism to control access to SQL server.
A user account is a security mechanism to control access to databases once a
connection has been established to SQL Server with a login. That is why
logins are mapped to user accounts.
Logins can be added to server roles to give that login rights to perform
server level operations -- adding logins/users, adding databases, etc.
Users are added to database roles since you need a user account to connect
to the database. DBO is an important one -- database owner.
Don't forget to read up on groups -- especially public.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?|||"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?<
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion:
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?<
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.|||> "mt" <anonymous@.discussions.microsoft.com> wrote in message
Why
(I
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion (watch line wrap):
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.
Steve|||MT,
As a newbie on SQL server ( I'm an Oracle DBA ) I found out that the online
docs where prety clear on how to log into the server / database(s) Please re
ad the "Permissions Validation" part in the "Administring SQLserver/Managing
security /Security levels"
ser account created in each database the login is accessing'. As long as on
ly one login can be used in a db and a user is associated with a login, what
is the purpose of having a
distinction between a User and a Login? Why can't you just add a Login to a
database and skip the User? Also, what is the purpose of allowing the user
name to be different than the login name (I realize it defaults to the login
name)? Since connection
strings use the login name, what purpose is there to have the username poten
tially different?
I'm assuming this has something to do with database (as opposed to server) r
oles, in which case multiple users can be added to a role. However, why are
n't Logins added directly to roles instead of Users?Sorry, but you should try and keep this as simple as possible.
A login is a security mechanism to control access to SQL server.
A user account is a security mechanism to control access to databases once a
connection has been established to SQL Server with a login. That is why
logins are mapped to user accounts.
Logins can be added to server roles to give that login rights to perform
server level operations -- adding logins/users, adding databases, etc.
Users are added to database roles since you need a user account to connect
to the database. DBO is an important one -- database owner.
Don't forget to read up on groups -- especially public.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
quote:
> In the Books Online, it is stated that '...a single login is mapped to one
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?
quote:
> I'm assuming this has something to do with database (as opposed to server)
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?|||"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
quote:
> In the Books Online, it is stated that '...a single login is mapped to one
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?<
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion:
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
quote:
> I'm assuming this has something to do with database (as opposed to server)
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?<
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.|||> "mt" <anonymous@.discussions.microsoft.com> wrote in message
quote:
> news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
one[QUOTE]
> user account created in each database the login is accessing'. As long as
> only one login can be used in a db and a user is associated with a login,
> what is the purpose of having a distinction between a User and a Login?
Why
quote:
> can't you just add a Login to a database and skip the User? Also, what is
> the purpose of allowing the user name to be different than the login name
(I
quote:
> realize it defaults to the login name)? Since connection strings use the
> login name, what purpose is there to have the username potentially
> different?<
>
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion (watch line wrap):
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
quote:
>
server)[QUOTE]
> roles, in which case multiple users can be added to a role. However, why
> aren't Logins added directly to roles instead of Users?<
>
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.
quote:
>
Steve|||MT,
As a newbie on SQL server ( I'm an Oracle DBA ) I found out that the online
docs where prety clear on how to log into the server / database(s) Please re
ad the "Permissions Validation" part in the "Administring SQLserver/Managing
security /Security levels"
Friday, February 24, 2012
Login Rejected
Hello,I develop my application on two different workstations (one at work and one at home). On each, I created a database with the same name, and added identical users with the same login ID and password. For simplicity, let's just say the database is called "my_data", and the user is "my_user" with a password of "12345". Recently, for reasons beyond this post, I needed to copy the data from my work database to my home database. I tried both a Detach Database, and a Full Database Backup, and have gotten the following result: When I attach/restore the databases to my home computer, the login "my_user" and "12345" fails. I THINK that even though my databases have users with the same login ID and password in them, that the two users are actually DIFFERENT users because they were created on different servers. I tried to remove the user from the database, but the db server wouldn't let me since the user owned a schema (which I can't uncheck in user preferences because it is grayed out). So I think I need to figure out how to remove the user that is in the restored database, and re-add the user that exists on the server that the database was restored to. Any suggestions? How do I remove the owned schema from the user that is in the database? Thanks! Mike
Hi,
when you use a SQL Database User (not an active directory user), the SQL Server generates SID's for the users. In your case the SQL Server generates two different SID's for the user. You can try to use the following stored procedure to synchronise the SID's:
- sp_change_users_login 'report' -> Displays all missing users
- sp_change_users_login 'Auto_Fix', 'HERE_USER_NAME', NULL, ''
See the msdn site (http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx) for more information.
Regards
Marc André
Login problems after Restore
I restored a DB from one SQL server to the other. Before doing the Restore I created a blank DB on the destination server. The destination server already had all the logins that were present on the source server. I also created the same users in the destination DB as the source. Before doing the Restore if I look at the users in the Enterprise manager I can see all the users that I created. However after the Restore the only user I see in the Enterprise manager is 'dbo'! If I login using isql and go the newly restored DB and issue 'sp_helpuser', it shows me all the users that I created.
So why don't I see these users from Enterprise manager?? It looks like an ID mismatch problem between syslogins and sysusers.
Next I login using ISQL to the destination server using one of the corrupted? logins. I try to go to the restored DB and it complains that this is not a valid user. So I come out and login in again as SA and go the restored database and try to add the earlier user. It says user already exists. :mad: I'm not sure whats the right way out here. :confused: Any help appreciated.
ThanksIssue sp_change_users_login 'report' which will display all unmapped SQL Server Standard Security-based users that need to be fixed. You can either issue sp_change_users_login 'Update_One', <userid>, <login>, or generate a script with the above syntax.
So why don't I see these users from Enterprise manager?? It looks like an ID mismatch problem between syslogins and sysusers.
Next I login using ISQL to the destination server using one of the corrupted? logins. I try to go to the restored DB and it complains that this is not a valid user. So I come out and login in again as SA and go the restored database and try to add the earlier user. It says user already exists. :mad: I'm not sure whats the right way out here. :confused: Any help appreciated.
ThanksIssue sp_change_users_login 'report' which will display all unmapped SQL Server Standard Security-based users that need to be fixed. You can either issue sp_change_users_login 'Update_One', <userid>, <login>, or generate a script with the above syntax.
Login problem: MSDE database from ASP.Net
I have created an MSDE database and successfully used it with an VB.Net
program on my desktop. Works great.
I have just begun learning ASP.Net. I created another MSDE database. But I
can't get past the login problems. "Predict" is the name of the database.
"GARY" is the Windows user account name. The error message is as follows:
Cannot open database requested in login 'Predict'. Login fails. Login failed
for user 'GARY\ASPNET'.
I used the following 2 connection strings. Neither works.
data source=(local)\VSdotNET;database=Predict;integrate d security=true
workstation id=GARY;packet size=4096;integrated security=SSPI;data
source="GARY\VSDOTNET";persist security info=False;initial catalog=Predict
Does anyone know what MSDE might be looking for here?
hi Gary,
Gary Frank wrote:
> I have created an MSDE database and successfully used it with an
> VB.Net program on my desktop. Works great.
> I have just begun learning ASP.Net. I created another MSDE database.
> But I can't get past the login problems. "Predict" is the name of
> the database. "GARY" is the Windows user account name. The error
> message is as follows:
> Cannot open database requested in login 'Predict'. Login fails. Login
> failed for user 'GARY\ASPNET'.
> I used the following 2 connection strings. Neither works.
> data source=(local)\VSdotNET;database=Predict;integrate d security=true
> workstation id=GARY;packet size=4096;integrated security=SSPI;data
> source="GARY\VSDOTNET";persist security info=False;initial
> catalog=Predict
> Does anyone know what MSDE might be looking for here?
you have to grant the account trying to log in (GARY\ASPNET) login
privileges as long as granting hin db access to the required databases..
as you can see from the raised error, using WinNT trusted authentication via
asp, you are not using the account you are logged in with, but the account
IIS is running on..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3jdk15Fp8sp6U1@.individual.net...
> hi Gary,
> Gary Frank wrote:
> you have to grant the account trying to log in (GARY\ASPNET) login
> privileges as long as granting hin db access to the required databases..
> as you can see from the raised error, using WinNT trusted authentication
> via asp, you are not using the account you are logged in with, but the
> account IIS is running on..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
Thanks Andrea. I am using MSDE and not SQL Server. As I understand it,
MSDE does not come with comprehensive administration tools. I can't figure
out how to grant access using MSDE. Are you aware of any tools I can
download or pay for without buying SQL Server, in order to grant database
access to a Windows user account?
|||hi Gary,
Gary Frank wrote:
> Thanks Andrea. I am using MSDE and not SQL Server. As I understand
> it, MSDE does not come with comprehensive administration tools. I
> can't figure out how to grant access using MSDE. Are you aware of
> any tools I can download or pay for without buying SQL Server, in
> order to grant database access to a Windows user account?
you can use the command line tool, oSql.exe, provided with MSDE, like
described in
http://support.microsoft.com/default...EN-US;q325003, or you can
have a look at a free prj of mine, available at the link following my
sign... other tools,, both free and commercial, are listed at
http://www.microsoft.com/sql/msde/partners/ and/or
http://www.aspfaq.com/show.asp?id=2442..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
program on my desktop. Works great.
I have just begun learning ASP.Net. I created another MSDE database. But I
can't get past the login problems. "Predict" is the name of the database.
"GARY" is the Windows user account name. The error message is as follows:
Cannot open database requested in login 'Predict'. Login fails. Login failed
for user 'GARY\ASPNET'.
I used the following 2 connection strings. Neither works.
data source=(local)\VSdotNET;database=Predict;integrate d security=true
workstation id=GARY;packet size=4096;integrated security=SSPI;data
source="GARY\VSDOTNET";persist security info=False;initial catalog=Predict
Does anyone know what MSDE might be looking for here?
hi Gary,
Gary Frank wrote:
> I have created an MSDE database and successfully used it with an
> VB.Net program on my desktop. Works great.
> I have just begun learning ASP.Net. I created another MSDE database.
> But I can't get past the login problems. "Predict" is the name of
> the database. "GARY" is the Windows user account name. The error
> message is as follows:
> Cannot open database requested in login 'Predict'. Login fails. Login
> failed for user 'GARY\ASPNET'.
> I used the following 2 connection strings. Neither works.
> data source=(local)\VSdotNET;database=Predict;integrate d security=true
> workstation id=GARY;packet size=4096;integrated security=SSPI;data
> source="GARY\VSDOTNET";persist security info=False;initial
> catalog=Predict
> Does anyone know what MSDE might be looking for here?
you have to grant the account trying to log in (GARY\ASPNET) login
privileges as long as granting hin db access to the required databases..
as you can see from the raised error, using WinNT trusted authentication via
asp, you are not using the account you are logged in with, but the account
IIS is running on..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3jdk15Fp8sp6U1@.individual.net...
> hi Gary,
> Gary Frank wrote:
> you have to grant the account trying to log in (GARY\ASPNET) login
> privileges as long as granting hin db access to the required databases..
> as you can see from the raised error, using WinNT trusted authentication
> via asp, you are not using the account you are logged in with, but the
> account IIS is running on..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
Thanks Andrea. I am using MSDE and not SQL Server. As I understand it,
MSDE does not come with comprehensive administration tools. I can't figure
out how to grant access using MSDE. Are you aware of any tools I can
download or pay for without buying SQL Server, in order to grant database
access to a Windows user account?
|||hi Gary,
Gary Frank wrote:
> Thanks Andrea. I am using MSDE and not SQL Server. As I understand
> it, MSDE does not come with comprehensive administration tools. I
> can't figure out how to grant access using MSDE. Are you aware of
> any tools I can download or pay for without buying SQL Server, in
> order to grant database access to a Windows user account?
you can use the command line tool, oSql.exe, provided with MSDE, like
described in
http://support.microsoft.com/default...EN-US;q325003, or you can
have a look at a free prj of mine, available at the link following my
sign... other tools,, both free and commercial, are listed at
http://www.microsoft.com/sql/msde/partners/ and/or
http://www.aspfaq.com/show.asp?id=2442..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, February 20, 2012
Login Problem
HI,
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Doller
doller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegro ups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>
|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller
|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>
|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller
|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>
|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Doller
doller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegro ups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>
|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller
|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>
|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller
|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>
|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
Login Problem
HI,
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
Login Problem
HI,
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
login permissions
I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
Dan D.
You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.
|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>
>
|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...[vbcol=seagreen]
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> Sysadmins will.
>
> Yes. But again, no need to add sysadmins.
>
> You can't do that.
>
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>
>
|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...[vbcol=seagreen]
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is[vbcol=seagreen]
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
one[vbcol=seagreen]
msdb and[vbcol=seagreen]
the[vbcol=seagreen]
of) the registry.[vbcol=seagreen]
one[vbcol=seagreen]
user[vbcol=seagreen]
msdb and[vbcol=seagreen]
master?[vbcol=seagreen]
the[vbcol=seagreen]
guest[vbcol=seagreen]
removed[vbcol=seagreen]
will[vbcol=seagreen]
that one[vbcol=seagreen]
for[vbcol=seagreen]
can I[vbcol=seagreen]
database.[vbcol=seagreen]
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
Dan D.
You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.
|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>
>
|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...[vbcol=seagreen]
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> Sysadmins will.
>
> Yes. But again, no need to add sysadmins.
>
> You can't do that.
>
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>
>
|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...[vbcol=seagreen]
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is[vbcol=seagreen]
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
one[vbcol=seagreen]
msdb and[vbcol=seagreen]
the[vbcol=seagreen]
of) the registry.[vbcol=seagreen]
one[vbcol=seagreen]
user[vbcol=seagreen]
msdb and[vbcol=seagreen]
master?[vbcol=seagreen]
the[vbcol=seagreen]
guest[vbcol=seagreen]
removed[vbcol=seagreen]
will[vbcol=seagreen]
that one[vbcol=seagreen]
for[vbcol=seagreen]
can I[vbcol=seagreen]
database.[vbcol=seagreen]
login permissions
I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for tha
t
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb an
d
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a gues
t
> user. This cannot be removed from master or tempdb however it can be remov
ed
> from msdb. Note that if you do remove it from msdb then only sysadmins wil
l
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one">
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb
and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query th
e
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of
) the registry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...[vbcol=seagreen]
> I'm not sure that I understand. If I remove the guest user from msdb no on
e
> will be able to create/manage job and save dts packages. If I add each use
r
> who I want to be able to create/manage jobs and save dts packages to msdb
and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query th
e
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change an
y
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> Sysadmins will.
>
> Yes. But again, no need to add sysadmins.
>
> You can't do that.
>
> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want th
em
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permis
sions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...[vbcol=seagreen]
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want th
em
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is[vbcol=seagreen]
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>
one[vbcol=seagreen]
msdb and[vbcol=seagreen]
the[vbcol=seagreen]
of) the registry.[vbcol=seagreen]
one[vbcol=seagreen]
user[vbcol=seagreen]
msdb and[vbcol=seagreen]
master?[vbcol=seagreen]
the[vbcol=seagreen]
guest[vbcol=seagreen]
removed[vbcol=seagreen]
will[vbcol=seagreen]
that one[vbcol=seagreen]
for[vbcol=seagreen]
can I[vbcol=seagreen]
database.[vbcol=seagreen]
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for tha
t
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb an
d
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a gues
t
> user. This cannot be removed from master or tempdb however it can be remov
ed
> from msdb. Note that if you do remove it from msdb then only sysadmins wil
l
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one">
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb
and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query th
e
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of
) the registry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...[vbcol=seagreen]
> I'm not sure that I understand. If I remove the guest user from msdb no on
e
> will be able to create/manage job and save dts packages. If I add each use
r
> who I want to be able to create/manage jobs and save dts packages to msdb
and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query th
e
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change an
y
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> Sysadmins will.
>
> Yes. But again, no need to add sysadmins.
>
> You can't do that.
>
> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want th
em
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permis
sions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...[vbcol=seagreen]
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want th
em
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is[vbcol=seagreen]
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>
one[vbcol=seagreen]
msdb and[vbcol=seagreen]
the[vbcol=seagreen]
of) the registry.[vbcol=seagreen]
one[vbcol=seagreen]
user[vbcol=seagreen]
msdb and[vbcol=seagreen]
master?[vbcol=seagreen]
the[vbcol=seagreen]
guest[vbcol=seagreen]
removed[vbcol=seagreen]
will[vbcol=seagreen]
that one[vbcol=seagreen]
for[vbcol=seagreen]
can I[vbcol=seagreen]
database.[vbcol=seagreen]
login permissions
I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >I created a new user and wanted them to only be able to access one
> >database.
> > For that one database on the "database access" tab I checked only that one
> > database and checked the "public" and "db_datareader" database roles for
> > that
> > one database.
> >
> > When I log in through QA they can also see the "master", "msdb" and
> > "tempdb"
> > databases. And they run select queries on the "master" database. How can I
> > prevent this? I only want them to be able to "see" the "stone" database.
> >
> > Thanks,
> > --
> > Dan D.
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>> You can't. They have access to these databases because they contain a guest
>> user. This cannot be removed from master or tempdb however it can be removed
>> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> be able to create/manage jobs and save DTS packages to the server.
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >I created a new user and wanted them to only be able to access one
>> >database.
>> > For that one database on the "database access" tab I checked only that one
>> > database and checked the "public" and "db_datareader" database roles for
>> > that
>> > one database.
>> >
>> > When I log in through QA they can also see the "master", "msdb" and
>> > "tempdb"
>> > databases. And they run select queries on the "master" database. How can I
>> > prevent this? I only want them to be able to "see" the "stone" database.
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages.
> Sysadmins will.
>
> > If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok?
> Yes. But again, no need to add sysadmins.
>
> > What happens if I remove guest from master?
> You can't do that.
>
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases.
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages. If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok? What happens if I remove guest from master?
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases. Is there a reason?
> >
> > Thanks,
> >
> > "Jasper Smith" wrote:
> >
> >> You can't. They have access to these databases because they contain a guest
> >> user. This cannot be removed from master or tempdb however it can be removed
> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
> >> be able to create/manage jobs and save DTS packages to the server.
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >> >I created a new user and wanted them to only be able to access one
> >> >database.
> >> > For that one database on the "database access" tab I checked only that one
> >> > database and checked the "public" and "db_datareader" database roles for
> >> > that
> >> > one database.
> >> >
> >> > When I log in through QA they can also see the "master", "msdb" and
> >> > "tempdb"
> >> > databases. And they run select queries on the "master" database. How can I
> >> > prevent this? I only want them to be able to "see" the "stone" database.
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages. If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok? What happens if I remove guest from master?
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a guest
>> >> user. This cannot be removed from master or tempdb however it can be removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only that one
>> >> > database and checked the "public" and "db_datareader" database roles for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How can I
>> >> > prevent this? I only want them to be able to "see" the "stone" database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages. If I add each
user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok? What happens if I remove guest from
master?
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a
guest
>> >> user. This cannot be removed from master or tempdb however it can be
removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins
will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only
that one
>> >> > database and checked the "public" and "db_datareader" database roles
for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How
can I
>> >> > prevent this? I only want them to be able to "see" the "stone"
database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >I created a new user and wanted them to only be able to access one
> >database.
> > For that one database on the "database access" tab I checked only that one
> > database and checked the "public" and "db_datareader" database roles for
> > that
> > one database.
> >
> > When I log in through QA they can also see the "master", "msdb" and
> > "tempdb"
> > databases. And they run select queries on the "master" database. How can I
> > prevent this? I only want them to be able to "see" the "stone" database.
> >
> > Thanks,
> > --
> > Dan D.
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>> You can't. They have access to these databases because they contain a guest
>> user. This cannot be removed from master or tempdb however it can be removed
>> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> be able to create/manage jobs and save DTS packages to the server.
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >I created a new user and wanted them to only be able to access one
>> >database.
>> > For that one database on the "database access" tab I checked only that one
>> > database and checked the "public" and "db_datareader" database roles for
>> > that
>> > one database.
>> >
>> > When I log in through QA they can also see the "master", "msdb" and
>> > "tempdb"
>> > databases. And they run select queries on the "master" database. How can I
>> > prevent this? I only want them to be able to "see" the "stone" database.
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages.
> Sysadmins will.
>
> > If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok?
> Yes. But again, no need to add sysadmins.
>
> > What happens if I remove guest from master?
> You can't do that.
>
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases.
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages. If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok? What happens if I remove guest from master?
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases. Is there a reason?
> >
> > Thanks,
> >
> > "Jasper Smith" wrote:
> >
> >> You can't. They have access to these databases because they contain a guest
> >> user. This cannot be removed from master or tempdb however it can be removed
> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
> >> be able to create/manage jobs and save DTS packages to the server.
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >> >I created a new user and wanted them to only be able to access one
> >> >database.
> >> > For that one database on the "database access" tab I checked only that one
> >> > database and checked the "public" and "db_datareader" database roles for
> >> > that
> >> > one database.
> >> >
> >> > When I log in through QA they can also see the "master", "msdb" and
> >> > "tempdb"
> >> > databases. And they run select queries on the "master" database. How can I
> >> > prevent this? I only want them to be able to "see" the "stone" database.
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages. If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok? What happens if I remove guest from master?
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a guest
>> >> user. This cannot be removed from master or tempdb however it can be removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only that one
>> >> > database and checked the "public" and "db_datareader" database roles for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How can I
>> >> > prevent this? I only want them to be able to "see" the "stone" database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages. If I add each
user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok? What happens if I remove guest from
master?
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a
guest
>> >> user. This cannot be removed from master or tempdb however it can be
removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins
will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only
that one
>> >> > database and checked the "public" and "db_datareader" database roles
for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How
can I
>> >> > prevent this? I only want them to be able to "see" the "stone"
database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>
Subscribe to:
Posts (Atom)