Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Remove user from read only db

Author  Topic 

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-06-26 : 16:03:37
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

12543 Posts

Posted - 2003-06-26 : 16:17:43
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

38200 Posts

Posted - 2003-06-26 : 16:18:51
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

265 Posts

Posted - 2003-06-26 : 16:48:00
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

12543 Posts

Posted - 2003-06-26 : 18:10:38
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

38200 Posts

Posted - 2003-06-26 : 18:34:13
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

265 Posts

Posted - 2003-06-26 : 19:44:37
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

38200 Posts

Posted - 2003-06-26 : 19:57:12
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

38200 Posts

Posted - 2003-06-26 : 20:07:06
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

12543 Posts

Posted - 2003-06-27 : 11:26:09
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

12543 Posts

Posted - 2003-06-27 : 16:17:37
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
   

- Advertisement -