Database Roles being modified by unknown process

I have two databases on two separate servers, with Merge replication running
between them. DB1 and DB2 both have the same 3 roles I created for user
access. Twice within the last week, the roles on DB2, the subscriber, have
changed removing most of their table permissions, but not all. Does anyone
know of something that could be causing this?
Thanks
Profiler is your friend - yo can trace Grant, Revoke and Deny commands like
all other commands and procedures. Check the "Security Audit Event Classes"
topic in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"kahunaVA" <kahunaVA@.discussions.microsoft.com> wrote in message
news:6A920DFD-EFDC-4D23-8D97-4B51E65F6F40@.microsoft.com...
> I have two databases on two separate servers, with Merge replication
running
> between them. DB1 and DB2 both have the same 3 roles I created for user
> access. Twice within the last week, the roles on DB2, the subscriber,
have
> changed removing most of their table permissions, but not all. Does anyone
> know of something that could be causing this?
> Thanks

Database Replication SQL 2000

I have two SQL 2000 servers. I want to make the primary and one the
secondary. I want to databases to stay replicated. If the Primary goes down
the Secondary would be used while we fix the Primary. Once the the Primary
is fixed it would be brought back online and the Secondary would replicate
it's data to the Primary. Then they would be in sync again.
What are my options here? What is the recomended way to accomplish this?
Thanks,
Scott
Scott,
there are many threads (and opinions!) on this sort of thing over the last
few months. I would recommend looking at queued updating subscribers
provided you aren't using BLOB datatypes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use Transaction Replication with updating Subscriber or use Log Shipping.

Database Replication

I'm a newbie so bare with me. If I have two servers running SQL Server, a primary and a secondary, how can I make sure that both databases (One on each server) are always syncronized? In our situation the secondary server is a hot backup in case the primary fails. If the primary fails then data gets written to the secondary. When the primary comes back up then its database needs to be synced with the secondary. Can anyone tell me how to do this?Have a look on books online about Transactional Replication...

This is a form of replication that will make your two server in sync.

Any changes that are made on one server are automatically replicated over to your other server.

http://www.databasejournal.com/features/mssql/article.php/1438201

http://www.sql-server-performance.com/transactional_replication.asp

Hope this helps mate|||Thanks for the links! Do you know if you are doing transactional replication, if the other server is down, will the server que the transactions and keep trying to send them until it succeees?|||no,

As far as I know it will publish on an all or nothing basis.

If your primary server goes down then replication will stop because it has no server to replicate to (i.e no subscribers)

This is the time you flip the primary to your hot spare and fix the original server. After you have sorted the problem on the original server just kick off replication again.

Cheers|||Why don't you create a 2 node cluster?|||Not using Enterprise. I don't think it is available on standard.|||You're right, Only on Enterprize.

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). Any
suggestions would be greatly appreciated.There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). Any
suggestions would be greatly appreciated.
There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). An
y
suggestions would be greatly appreciated.There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

database owner is needed

hi,

on 1 of my servers (actually, the dev. server I have setup up here at home), I must included the database owner everytime I select something or make a db call.

for example

select T.foo from K inner join T on T.id = K.id

must actualy be written like:

select dbo.T.foo from dbo.K inner join dbo.T on dbo.T.id = dbo.K.id

or else it wont work

what did I do wrong for this database owner thing to be a "must" when writing queries on this server.

spec:
sql 2000 sp4You are not connecting as a dbo user.|||

That's wierd. What error are you getting? It should check the dbo owned objects after checking for an object owned by you.

|||

Amir, please update thread.

Thanks,

Derek

|||sorry folks,

I was tied up on another project for the week. I came back and tried to

trouble shoot instead of wasting you guy's time but at the end, i

failed.
basically, here is the situation:

the actual server that will end up hosting the project is setup fine so if i leave out the dbo.* or user.* and just type select * from table it works.

right now, i'm resorting to including the dbo in my queries... and evertime i upload to the server, i do a search in that folder and replace " dbo." with "" in all files.

let me explain how i made this database:

originally, it was on a dev server somewhere in US...
i made the db on my home server. Then created a user and gave it admin privilages over that db.
Then i did "All Tasks > Import" and imported the tables from that server, to this new home db. I had to go back and manually select the primary key for each table, as they got lost during the transfer.

now, when i look at the "Server Explorer" cluster of tables in this new database, i see that they all have (dbo) beside their names, meaning the owner of each table by default is dbo.

i think that should pretty much cover everything....
any clue as to why user "must" be specified?|||

What do you get on the server that requires dbo. when you execute:

select suser_sname(), user_name()

Also, for some object where you have to enter dbo. for the object, execute:

select *
from sysobjects
where name = '<name>'

select *
from information_schema.tables
where table_name = '<name>'

Perhaps this will shed some light? Also what is in @.@.version?

This might just be a stumper that requires a higher power :)

|||

> What do you get on the server that requires dbo. when you execute:

>select suser_sname(), user_name()

| __|_
| foouser | foouser

>select *
>from sysobjects
>where name = '<name>'

irrelevant

> select *
> from information_schema.tables
> where table_name = '<name>'

TABLE_SCHEMA is 'dbo' for all objects

conclusion:
objects where created as dbo when they were imported from the server on the hosting company to the local staging environment;

how can i prevent this when using SQL 2000 enterprise manager for import

(note: from my exp. with sql 2005, this problem wasn't encountered when going through the same steps -- or rather similar steps -- using native sql 2005 import/export functionality within the SQL 2005 studio)


side note:
i rather find a script based answer rather learning to use the GUI, because of my DB2, mysql background -- obviously i'm not gifted when using GUI tools