Login has public role but no access after RESTORE
-
Hi,
I was testing my warm-start server this morning. I ship database backups and logs to it at regular intervals. the SQl 2K database server uses mixed mode authentication.
This morning I issued the RESTORE DATABASE ... WITH RECOVERY and tried to connect with my login, without success.
When I checked, the login was a member of the public and db_owner roles, but did not have access to the database.
No probs I thought...
sp_grantdbaccess 'dbuser'
I got the response...
Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line 147
User or role 'dbuser' already exists in the current database.
OK, I know that, they are a member of the public role. Lets try removing the role...
sp_droprolemember 'public' ,'dbuser'
Server: Msg 15081, Level 16, State 1, Procedure sp_droprolemember, Line 26
Membership of the public role cannot be changed.
Nice... now what do I do folks? I can't drop the user from the 'public' role to remove them before I give them access, and I can't give them access while they are already in the public role.
David
If it ain't broke, don't fix it...
-
What is the default database for the account? If it is anything but master, try changing it to master, apply the change, then switch it back to the user database.
-
Thanks Ron,
I have tried that but it didn't work unfortunately.
I am thinking that because I initially set up the database using DTS export, and used the option to copy all the logins (which creates both the logins on the server and the dbaccess and role membership within the database), then used RESTORE DATABASE ... WITH STANDBY or RESTORE LOG ... WITH STANDBY, SQL Server has removed the db access but not the membership of the roles within the database. effectively I have created 'orphaned' role membership, even though the login still exists.
I am going to test this to see if this is the problem by trying to reproduce what happened.
David
If it ain't broke, don't fix it...
-
Ok,
as I suspected. After initially creating the database with DTS, when I ship copies of database and transaction logs and restore using RESTORE ...WITH STANDBY, the database access granted to the login is lost, resulting in a login with orphaned role membership in that database.
So, i need to be able to copy the database without the login information, and then apply the login and role membership after I have RECOVERed the database. I could copy the logins from the production database, but since this is missing presumed dead in the scenario I am testing, and they are SQL Server logins not Windows accounts, I think it is probably easiest to create them in a script.
PS: oh the joys of writing your own home-grown log shipping tool in Standard Edition SQL 2K
David
If it ain't broke, don't fix it...
-
I would like to say your restore broke the relationship between users and logins (orphan users). You may run the following script to see if your login is in the list,
sp_change_users_login 'report'
If the answer is yest, you can run the following script to rebuild the relationship,
sp_change_users_login 'userID', 'loginID'
-
Hi SQL Oracle,
Thanks for that information, you were right about the user being orphaned from the login.
I checked the sp out in BOL and used the following syntax...
sp_change_users_login 'Update_One' ,'dbuser', 'dbuser'
which re-made the link, and hey presto
Cheers
David
PS: have you ever tried the 'Auto_Fix' action?
If it ain't broke, don't fix it...
-
In my previous message, I missed the part, 'Update_one'.
I tried 'Auto_Fix', but it was no good.
-
I always use the auto_fix and it works for me.
sp_change_users_login 'auto_fix', 'user'slogin'
-SQLBill
-
Thanks for all your help guys.
I managed to resolve the problem using the solution you provided. We are upgrading our production server from Win2k to Windows 2003 next week, so I was keen to know that the warm-start backup server was viable should it all go pear-shaped.
David
If it ain't broke, don't fix it...
-
I checked the sp out in BOL and used the following syntax... sp_change_users_login 'Update_One' ,'dbuser', 'dbuser'
I'd like to say thanks for this. From the messages I've seen the helpees don't always say thanks to the helpers.
I'm temporarily taken over the role of dba after ours decided he'd had enough and gone snowboarding and cycling round Europe (jelous - me?!). I knew there must be an easier way of rejoining the orphaned logons after a live to test copy. Just tried it and it works. Time to add this to the end of the live-to-test SP.
-
Morning all:
Postscript on the upgrade to Windows 2K3 of my SQL2K server.
Everything seems to work with a couple of strange exceptions. My DR file shipping procedure, which uses xp_cmshell 'XCOPY ...' to copy the file across to the target server is refusing to recognise the mapped network drive any more. Logging in on the server using my SQLAgent account, and running SQA confirmed that master..xp_cmdshell 'DIR G:\' gave me the return 'Invalid drive specification', even though the drive was mapped, and accessable to that account from the command prompt. Replacing the G:\ with the full UNC path name resolved the problem, but why???
My 3rd party client application also runs from from all the network PCs, but when I run it on the database server (which I do once a month to do a full export of the system using the apps method) it kills the app at the point where it is trying to connect to the database
When i have solved these problems I will have a look at how Win2k3 is managing the SQL Server memory, to see if there are any differences.
David
If it ain't broke, don't fix it...
-
Hi David
we had the same situation before, (It was quite a long time ago). As a work around, try this:
EXEC
master..xp_cmdshell 'net use x: \\servername\sharename'
EXEC
master..xp_cmdshell 'net use'
EXEC
master..xp_cmdshell 'dir x:'
EXEC
master..xp_cmdshell 'net use x: /delete'
If your drives get mapped from the logon script, you could try
EXEC master..xp_cmdshell '%logonserver%\netlogon\logonscriptname.bat'
(Theres probably a tidier way of doing this)
-
Hi Wayne,
thanks for that tip, it certainly worked from SQA. I am speculating that for some reason the Win2K3 environment is not accessible to the command shell as executed from SQA or SQLServer Agent.
On my other issue, my third part app, I suspect it has fallen foul of Win2K3 Data Execution Prevention (DEP) settings (Advanced tab, then Performance Settings button in the My Computer properties). I have changed them, but will need to reboot the server before I can see if I am correct.
David
If it ain't broke, don't fix it...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply