I have been trying to set up permissions for a SQL user ID
on a specific table. The user will access this table using
MS Access 2000 with a standard ODBC connection. I only
want the user to be able to view data from this table.
When I set up a user role in SQL associated with this
user, the permissions on this role are set to deny insert,
update and delete capabilities. This does not appear to
work. However, if I change the permissions on the public
role to deny insert, update, and delete capabilities this
works.
I know every user assumes the public role but I can't seem
to override the permissions set on the public role.
The SQL books online documentation states that a denied
permission always takes prcedent. I cannot seem to make
this work when I have a specific role for this user which
has the deny permissions set.
Thanks,
Jim d'HulstIs the user perhaps an admin on the server and thus inheriting sysadmin via
the Builtin\System role created by default? ( I normall remove this pretty q
uickly)
Alicia
www.sqlporn.co.uk|||Alicia, the user is jnot an admin on the server nor does
this SQL user id belong to any system administration roles.
What I find is if I use windows NT authentication and
assign the permissions the a windows users it works fine.
It is just when I use a SQL user ID that this doesn't seem
to work.
Thanks,
Jim
>--Original Message--
>Is the user perhaps an admin on the server and thus
inheriting sysadmin via the Builtin\System role created by
default? ( I normall remove this pretty quickly)
>Alicia
>www.sqlporn.co.uk
>.
>|||Jim,
Overriding public should be quite doable. Since it is working for your
domain account, I would expect it to work for a SQL account as well. With
all due deference, I suggest that it is probably something simple, but just
hard to see. Silly possibilities:
There is a guest account and the user name is misspelled such that the user
comes in as guest.
The user is also in the db_owner role for the database.
Russell Fields
"jim.dhulst@.am.dynonobel.com" <anonymous@.discussions.microsoft.com> wrote in
message news:fba601c43e7d$96ba1c70$a301280a@.phx.gbl...[vbcol=seagreen]
> Alicia, the user is jnot an admin on the server nor does
> this SQL user id belong to any system administration roles.
> What I find is if I use windows NT authentication and
> assign the permissions the a windows users it works fine.
> It is just when I use a SQL user ID that this doesn't seem
> to work.
> Thanks,
> Jim
> inheriting sysadmin via the Builtin\System role created by
> default? ( I normall remove this pretty quickly)
Showing posts with label conflicts. Show all posts
Showing posts with label conflicts. Show all posts
Friday, March 30, 2012
Resolver Issues
I'm not able to resolve conflicts using the conflict viewer because I have
timestamp columns in my tables. Can I manually resolve these conflicts by
making the necessary changes to the data?
I'm using filters on my articles and I'm noticing that replication is
changing rows in the table that don't meet my filter clause. Well, all
the data didn't change in the row just the Last_Update_Date and
Last_Update_By columns changed. I have a trigger on my tables that
updates these columns. For some reason my trigger is getting fired.
These two columns get updated so replication thinks it needs to push these
changes along to the subscriber. I end up with conflicts if the
subscriber has also changed these rows.
The first thing to figure out is what is causing teh triggers to fire.
As far as manually doing this, you can. The conflict viewer is just another
application which sits over a set of data. You can easily do this yourself.
You can freely read/write to the conflict tables if you choose since the
only things which reads from them is the conflict viewer. If you choose,
you can write your own application to replace the conflict viewer in order
to fit it into your environment and tailor it to your needs.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I figured out my trigger issue.
I appreciate your help.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:O4wRnlLKEHA.620@.tk2msftngp13.phx.gbl...
> The first thing to figure out is what is causing teh triggers to fire.
> As far as manually doing this, you can. The conflict viewer is just
another
> application which sits over a set of data. You can easily do this
yourself.
> You can freely read/write to the conflict tables if you choose since the
> only things which reads from them is the conflict viewer. If you choose,
> you can write your own application to replace the conflict viewer in order
> to fit it into your environment and tailor it to your needs.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
timestamp columns in my tables. Can I manually resolve these conflicts by
making the necessary changes to the data?
I'm using filters on my articles and I'm noticing that replication is
changing rows in the table that don't meet my filter clause. Well, all
the data didn't change in the row just the Last_Update_Date and
Last_Update_By columns changed. I have a trigger on my tables that
updates these columns. For some reason my trigger is getting fired.
These two columns get updated so replication thinks it needs to push these
changes along to the subscriber. I end up with conflicts if the
subscriber has also changed these rows.
The first thing to figure out is what is causing teh triggers to fire.
As far as manually doing this, you can. The conflict viewer is just another
application which sits over a set of data. You can easily do this yourself.
You can freely read/write to the conflict tables if you choose since the
only things which reads from them is the conflict viewer. If you choose,
you can write your own application to replace the conflict viewer in order
to fit it into your environment and tailor it to your needs.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I figured out my trigger issue.
I appreciate your help.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:O4wRnlLKEHA.620@.tk2msftngp13.phx.gbl...
> The first thing to figure out is what is causing teh triggers to fire.
> As far as manually doing this, you can. The conflict viewer is just
another
> application which sits over a set of data. You can easily do this
yourself.
> You can freely read/write to the conflict tables if you choose since the
> only things which reads from them is the conflict viewer. If you choose,
> you can write your own application to replace the conflict viewer in order
> to fit it into your environment and tailor it to your needs.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
Wednesday, March 21, 2012
reseed required after solving conflicts
I have set-up several replications, all of them working on the same
way. But on the last one a strange thing occured: It seems that when a
conflict is solved by choosing to use the displayed data from the
looser, I need to reseed the identity column.
The replication is working on the merge model. On the two databases,
the identity is not for replication, with different seed ( 1 for the
main database, 2 for the other one) and an increment of 10.
The databases are SQL Server 2000, and the operating system is Windows
2000 server.
Could you please tell me why this reseed is required.
Regards
Patrick
Why do you say you need to reseed the identity column? What error message is
generated?
Or is what happens is that subsequent inserts pick the last inserted value?
For example you have an identity range of 10 on one side 1000 on another.
The 1001 row is merged to the publisher but is logged as a conflict and
rolled back. Then you select this conflict to win, and the 1001 row gets
inserted in the publisher. Then does the publisher's next value start at
1002?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick" <pmenage@.intnet.mu> wrote in message
news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...
>I have set-up several replications, all of them working on the same
> way. But on the last one a strange thing occured: It seems that when a
> conflict is solved by choosing to use the displayed data from the
> looser, I need to reseed the identity column.
> The replication is working on the merge model. On the two databases,
> the identity is not for replication, with different seed ( 1 for the
> main database, 2 for the other one) and an increment of 10.
> The databases are SQL Server 2000, and the operating system is Windows
> 2000 server.
> Could you please tell me why this reseed is required.
> Regards
> Patrick
>
|||After solving a confict on a table with an identity, I need to ressed
the identity of this table else, the identity of next record inserted
by the application takes the value just after the one of the identity
of the record on which there were conflicts. As there are other exiting
records, it is rejected due to primary key problem. It seems that when
solving a conflict, the seed is changed to the value of the identity of
the record on wich the conflict was solved.
To answer your second question, yes, the publishers's next value start
at 1002.
I set up the identity to start at 1 on the main database and at 2 on
the other database. On both side the increment is 10.
Looking forward to reading your thoughts on the above.
Best Regards
Patrick
Hilary Cotter wrote:[vbcol=seagreen]
> Why do you say you need to reseed the identity column? What error message is
> generated?
> Or is what happens is that subsequent inserts pick the last inserted value?
> For example you have an identity range of 10 on one side 1000 on another.
> The 1001 row is merged to the publisher but is logged as a conflict and
> rolled back. Then you select this conflict to win, and the 1001 row gets
> inserted in the publisher. Then does the publisher's next value start at
> 1002?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Patrick" <pmenage@.intnet.mu> wrote in message
> news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...
way. But on the last one a strange thing occured: It seems that when a
conflict is solved by choosing to use the displayed data from the
looser, I need to reseed the identity column.
The replication is working on the merge model. On the two databases,
the identity is not for replication, with different seed ( 1 for the
main database, 2 for the other one) and an increment of 10.
The databases are SQL Server 2000, and the operating system is Windows
2000 server.
Could you please tell me why this reseed is required.
Regards
Patrick
Why do you say you need to reseed the identity column? What error message is
generated?
Or is what happens is that subsequent inserts pick the last inserted value?
For example you have an identity range of 10 on one side 1000 on another.
The 1001 row is merged to the publisher but is logged as a conflict and
rolled back. Then you select this conflict to win, and the 1001 row gets
inserted in the publisher. Then does the publisher's next value start at
1002?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick" <pmenage@.intnet.mu> wrote in message
news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...
>I have set-up several replications, all of them working on the same
> way. But on the last one a strange thing occured: It seems that when a
> conflict is solved by choosing to use the displayed data from the
> looser, I need to reseed the identity column.
> The replication is working on the merge model. On the two databases,
> the identity is not for replication, with different seed ( 1 for the
> main database, 2 for the other one) and an increment of 10.
> The databases are SQL Server 2000, and the operating system is Windows
> 2000 server.
> Could you please tell me why this reseed is required.
> Regards
> Patrick
>
|||After solving a confict on a table with an identity, I need to ressed
the identity of this table else, the identity of next record inserted
by the application takes the value just after the one of the identity
of the record on which there were conflicts. As there are other exiting
records, it is rejected due to primary key problem. It seems that when
solving a conflict, the seed is changed to the value of the identity of
the record on wich the conflict was solved.
To answer your second question, yes, the publishers's next value start
at 1002.
I set up the identity to start at 1 on the main database and at 2 on
the other database. On both side the increment is 10.
Looking forward to reading your thoughts on the above.
Best Regards
Patrick
Hilary Cotter wrote:[vbcol=seagreen]
> Why do you say you need to reseed the identity column? What error message is
> generated?
> Or is what happens is that subsequent inserts pick the last inserted value?
> For example you have an identity range of 10 on one side 1000 on another.
> The 1001 row is merged to the publisher but is logged as a conflict and
> rolled back. Then you select this conflict to win, and the 1001 row gets
> inserted in the publisher. Then does the publisher's next value start at
> 1002?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Patrick" <pmenage@.intnet.mu> wrote in message
> news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...
Subscribe to:
Posts (Atom)