2 questions (Sharing and Permissions) about SQL Server 2000 databases

[djpingpong]

Hi all,

I've got 2 questions... maybe I should post it separately, but they could be kinda related.

Question 1:
Now, everyone is probably familiar with Folder sharing in Windows. The blue sharing hand that appears at the bottom of the folder icon... if you don't know what i'm talkin about, then you probably can't help me
Now, on my SQL Server 2000, under the Enterprise Manager, one of my DB has the same blue sharing hand under its icon. I can provide screenshot if no one understands what i'm talkin about. Anyhoo... my first question is: What the hell is that? How do I "share" a database and what does it do?

Question 2:
That same database (with the sharing icon) is our main production database... the life of this company depends on that DB (named "PROD_DB" running on production server "PRODSRV")
I have a test machine where i'm tryin to duplicate the production server machine PRODSRV. Now, the reason why i'm doing this is because we have an MS Access application that we use and it connects to PROD_DB. Well, I wanna make some changes/upgrade to the current application and we all know that [!]We should never test on a production server[/!]
So, I installed everything the exact same on the test machine "TESTSRV", including SQL Server 2000. I copied the "PROD_DB" over to my test machine using the Copy Database wizard and everything looked ok at first. However, my Access application has 2 (so far) new problems.
[ol]
[li]I don't have the sharing hand icon in the test version of the database, not sure if that icon matters or not[/li]
[li]Besides the "administrator" account, no one can access the database on TESTSRV. All other user accounts trying to use the MS Access application would get an error when trying to fetch information from the DB[/li]
[/ol]
Now, I thought I checked all the permission settings, but if anyone has any suggestions... please feel free to let me know. I hope I explained my situation clear enough

thanks to all that help

 

[ptheriault]

The hand means that your database is being replicated. You will want to find out what is going on there. Is it merge,transactional or snap shot. Open the replication monitor get some details on that.

The problem you are having on Dev is that your sids don't match.
Run this

exec sp_change_user_login 'REPORT'

It will return all you users that are out of sync.
You can correct them with this.

Run this for each user returned on the previous result set.

exec sp_change_users_login 'update_one', 'user', 'user'

- Paul
- Database performance looks fine, it must be the Network!

 

[djpingpong]

You're right.. the hand does mean replication. However, when i clicked on Replication Monitor, it asked me if I wanted to start the monitoring service... so, i'm assuming that no replication was actually going on since the service was stopped. Which, by the way, I don't know how to stop the service again... cuz I started the service to see if anything would change. Also, I dunno if it's merge, transactional or snap shot.

Now... i'm sorry, but i need to ask to obvious the code

exec sp_change_user_login 'REPORT'

I tried running that in Query Analyzer and it returned this error:
[!]Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_change_user_login'[/!]

Did I run the code with the correct application? I can't check the SIDs like you suggested

 

[ptheriault]

opps sorry about the type-o

that's
sp_change_users_login

user has an 's'

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Also, turning on the monitoring service is not the same as turning on replication. Check the jobs in the sql server agent. There will be a job for replication, if it is executing then your are currently replicating that database somewhere. Drill down into the replication monitor icon. It will provide you with details about the type of replication that is set up.


- Paul
- Database performance looks fine, it must be the Network!

 

[djpingpong]

Ok.. no worries about the replication issue... because there is nothing else to replicate... I got confirmation on that one.

Let's focus on the permission issue...
I ran the EXEC statement on both the Development and Production machine... both returned NOTHING!
So, does that mean that none of my users are out of sync?
What do i do now?

 

[ptheriault]

I forgot to mention that you must run that script from the user database. In your Case PROD_DB on the test server.
If you still don't get back any user account names then the sids are in sync. I would then move on to checking object level permissions. For example right click on an object and select properties -> permissions. This will show you which users have what permissions.
If nothing is checked then you will need to re-add permissions.
Also, just for furture consideration I find the best method of creating\refreshing a test db is to take the backup from prod and restore it to test. By using that method you are assured of not loosing any permissions. You might run into the SID problem but that is easy to correct.

- Paul
- Database performance looks fine, it must be the Network!

 

[djpingpong]

Haha... it's a good thing you showed me the EXEC command because then it ensured me that it wasn't the user accounts because everyone was in sync...

But i figured it out.. and i wish i saw it earlier cuz it seems so easy

It has nothing to do with the permission of the database... it has to do with the permission of each SQL Server Registration.

SQL Server Root -> Security -> Logins

I just added my user group and it worked....

thanks very much
until my next problem...

 

[ptheriault]

I'm glad you got it firgured out.


- Paul
- Database performance looks fine, it must be the Network!