SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Remove user from read only db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sarat
Constraint Violating Yak Guru

USA
265 Posts

Posted - 06/26/2003 :  16:03:37  Show Profile  Reply with Quote
Hi,
Since I have a logshipping setup, my copy/destination db is read-only but I have some users who have this db available in their db list via Query Analyzer. I want to remove this db from their list because when they accidentally log into this, the restore fails as it requires exclusive access.
but it is grayed out, as an admin, why can't i remove this db from their list? is it because it is in read-only state so I cannot alter any permissions so do i have to undo read only and then remove access and then put the db back to read-only?
Thanks,
Sarat.


**To be intoxicated is to feel sophisticated, but not be able to say it.**

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/26/2003 :  16:17:43  Show Profile  Visit nr's Homepage  Reply with Quote
The users are held in the system tables in the database - if the database is read only then you won't be able to update the system tables.

Setting the datbase to allow updates will mean completing recovery which will mean that you can't apply any more log files.

You could do something to change the login id maybe so that it is no longer mapped to the user in the database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36910 Posts

Posted - 06/26/2003 :  16:18:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes it is because the database is read-only. I don't think that changing the read-only status and then modifying it and then putting it back to read-only will work. Log shipping could fail if you do this or log shipping will overwrite what you have just done when the next transaction log gets applied.

Are you log shipping the database to the same server? What is the point of doing this?

Tara
Go to Top of Page

Sarat
Constraint Violating Yak Guru

USA
265 Posts

Posted - 06/26/2003 :  16:48:00  Show Profile  Reply with Quote
No I am not logshipping to the same server. It so happenend that the log shipping restore job started failing and the reason was 'Exclusive access could not be obtained to restore the log' so I checked Current Activity window to see what else is accessing the copy/destination database. I found that one of the users had connected to it through query analyzer so i killed the connection & then restore worked. So, I wanted to know if there is anyway to remove the db from user's login list to avoid this in future.
So I thought I can switch the db to write mode, remove it from user's list and put the db back to read-only but as you mentioned log shipping might fail which i think is ok as long as i can fix it later.
-Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/26/2003 :  18:10:38  Show Profile  Visit nr's Homepage  Reply with Quote
Can you update the database without completing recovery?
If not then you won't be able to continue applying logs.

When you say users list do you mean the user as an entry in the database - if not then it shouldn't matter that the database is read only as it will be a client entry which you can get rid of by updating the reqistry.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36910 Posts

Posted - 06/26/2003 :  18:34:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why don't you just remove the user from the server instead of from the database?

You can't update a database that is in standby mode. You would need to recover the database (this would take it out of read-only mode). By recovering the database, you have now broken log shipping because additional transaction logs can't be applied to a recovered database. There isn't a way around this.

Tara
Go to Top of Page

Sarat
Constraint Violating Yak Guru

USA
265 Posts

Posted - 06/26/2003 :  19:44:37  Show Profile  Reply with Quote
Well I can't remove the user from the server itself because there are other instances which the user requires access to! This seemed like a simple thing. Don't know now as to what I am going to do!
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36910 Posts

Posted - 06/26/2003 :  19:57:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
I still think that this can be resolved without modifying the database.

Nigel mentioned: "You could do something to change the login id maybe so that it is no longer mapped to the user in the database."

I believe that this would work although it would mean updating a system table. You can't run sp_droplogin because you would need to drop the user from the database first. The table that you would have to update is sysxlogins (or you could update syslogins which is a view into sysxlogins). Updating this table though is highly not recommended. What would be recommended is educating the users to not connect to servers that they aren't supposed to be connecting to.

You could also create a step in the RESTORE job that runs prior to the actual log shipping RESTORE. This step would loop through sysprocesses and KILL any connections to the log shipped database. I believe that someone has already posted code at SQLTeam that does this.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36910 Posts

Posted - 06/26/2003 :  20:07:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
I just found this script that was written at my last job that does this KILL thing I mentioned. It worked for 7.0, but I'm not sure if it works for 2000. Just change DB1 (in two locations) to the name of your database.


DECLARE @userid VARCHAR(3)

WHILE EXISTS (SELECT * from master..sysprocesses WHERE db_name(dbid) = 'DB1' AND spid <> @@spid)
BEGIN

SELECT @userid = CONVERT(VARCHAR(3),(SELECT MAX(spid)
FROM sysprocesses WHERE db_name(dbid) = 'DB1' AND spid <> @@spid))

EXEC ('kill ' + @userid )

END



Tara
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/27/2003 :  11:26:09  Show Profile  Visit nr's Homepage  Reply with Quote
You should make sure you aren't trying to kill any system processes.

I usually do this with a delay to check if the spids have died and retry a few times and give up if they stay there.



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/27/2003 :  16:17:37  Show Profile  Visit nr's Homepage  Reply with Quote
Looking at sp_droplogin it just does a load of checks then deletes the login from syslogins.

You could create a version that does the same but excludes your read only login from the check.

Then recreate the login - if it gets the same ID then delete it again, add a dummy login to take up that id then add the login again. This will have a different ID so won't map to the user in the database (your dummy one will).
Eventually you need to make sure an unused login maps to that user.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000