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.
| Author |
Topic |
|
rspinell
Starting Member
36 Posts |
 Posted - 2009-03-05 : 03:37:12
|
| Is there anyway to modify the sysuser table in SQL 2005 for a user DB? I know it’s not supported but I was able to perform the task in SQL 2000.We need to do this for a scenario in which we log ship a DB. With SQL 2000 we were able to allow access to the read only DB on the secondary via a local group on the server we created (group name is SQLLogShipRead) and not allow access to the DB on the primary. We accomplished this in SQL 2000 by modify the sysuser table on the DB (primary) and putting in the SID of the local group (SQLLogShipRead) from the secondary. When we log shipped the DB since the SID on the DB matched the SID for the local group (SQLLogShipRead) on the secondary the users in that group were able to have access to the DB. We didn't put any users into the SQLLogShipRead local group on the primary so they weren't able to access the DB on the primary (which is what we wanted).I have tried to perform the same thing on SQL 2005 but receive the error "Ad hoc updates to system catalogs are not allowed". I read you can use DAC in single user mode to make these changes, but when I did launch DAC I could only see the master DB and not my user DB that I need to modify.I understand this isn't supported and whatever I do is at my own risk, but need to find a way to have the same functionality I had in SQL 2000 in SQL 2005 for my log shipped DB or I won't be able to roll it out to production. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-03-05 : 07:23:29
|
| Sysusers isn't a table in SQL 2005. It's a view and the system tables are a lot more complicated and are hidden.I'm not completely clear on what change you want to make. Could you explain in a bit more detail? I think, if I'm reading correctly, there's a way without hacking anything, but I'm not sure.--Gail ShawSQL Server MVP |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-05 : 09:46:20
|
| why you need to modify sysusers? Can't you just restrict permission for that DB in Primary server? Playing with system tables can make your DB corrupt. |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2009-03-05 : 11:29:48
|
| Thanks for the response.Hope this makes it clearer: We currently have a product called SMS 2003 that leverages SQL 2000, the SQL DB for SMS is 305GB's. We log ship the DB twice a day to another box that is a replica, and the DB on the replica is in read only mode. The goal was to allow people in the organization read rights to the replica so they can do data pulls of the information but not allow the same read rights to the original SQL DB on the primary. Sodeep to your point, I can allow or disallow the users read rights to the primary DB but when I logship the DB those rights I assigned on the primary would end up on the replica. What I need to be able to have is different rights on the replica then what is on the primary. We did find a way to do this years back, MS sent us a way of doing it and said it’s not supported, but it did work. Below is the process that worked on SQL 2000 and the info below and under the **** is Microsoft's original response to this issue 2 years ago for SQL 2000.************The bottom line is that SID in the local group is different on everycomputer and can't be easily replicated. The workaround would involvefollowing steps:- create the local group with the same name on the destination server- use stored procedure from KB246133 to build the replication script- edit the computer name in the SP_GRANTLOGIN- modify system table to mach the logon's SID in master.dbo.sysxloginswith user account's SID in sysusers in the replicated database(procedure below)-------select * from master.dbo.sysxloginsselect * from sysusers (destination database)- Allow updates to the system databasesSP_CONFIGURE 'allow updates', 1RECOFIGURE WITH OVERRIDE- Update SID in SYSXLOGINSUPDATE master.dbo.sysxlogins set SID = sid_from_sysusersWHERE SID = sid_form_sysxlogins************This process no longer works in SQL 2005. I'm trying to get the same functionality with SQL2005 that I currently have with SQL 2000. I need to allow users read rights to the replica but not allow the same users read rights to the primary DB.Thank you |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2009-03-05 : 16:19:36
|
| I forgot to mention, I've also called MS on this issue and they said its not possible anymore in SQL 2005. |
|
|
|
|
|
|
|
|
Modify sysuser table for user DB in SQL2005?