March 11, 2009 at 10:11 am
Hi all,
I'm looking for a simple method of doing something, despite having a longer way to do it.
On occasion (more than I'd like to) I have to copy our production environment to a training/development environment.
So what I do is:
Detach my Training database and rename the mdf/ldf (in case 'they' forgot something and I need to bring it back quickly)
Backup of production
Restore the backup into my Training server
Now because I already had all my sql logins on the Training box, they're all there, and all the users come over on the restore, though what DOESN'T seem to work is the logins being tied correctly to the users.. I go into the logins and try to add access to the database, but can't because the user already exists in the database. So I have to go into the database security and remove the user, then go back to the login and give them access.
Is there a quicker way of doing this? I have a script somewhere which can pull over all sql logins/users from my production environment.. unfortunately, the users and logins already exist, so it fails.
Maybe what I need is a script to drop all users and sql logins from a database/server and then I can recreate them.. or something else?
Any help would be appreciated, its really starting to get on my dang nerves 🙂
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 11, 2009 at 10:46 am
I may be incorrect in my assumption but this looks like an orphaned users issue.
http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
March 11, 2009 at 10:50 am
that seems to be just what I was looking for.. just need to wrap the Report and the Autofix together and it should be a winner.
Thanks for the help 🙂
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 11, 2009 at 11:55 am
hmm.. hate to be a pest.. but.. I'm trying to set this up so it'll try and Autofix every orphan user..
Heres what I have:
CREATE TABLE dbo.temp_OrphanUsers
(
UserName varchar(250) NULL,
UserSID varchar(500) NULL
) ON [PRIMARY]
INSERT INTO dbo.temp_OrphanUsers
EXEC sp_change_users_login 'Report'
DECLARE curOU CURSOR FOR
SELECT UserName FROM dbo.temp_OrphanUsers
OPEN curOU
DECLARE @OUUserName VARCHAR(250)
FETCH NEXT FROM curOU INTO @OUUserName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
EXEC sp_change_users_login 'Auto_Fix', @OUUserName
END
FETCH NEXT FROM curOU INTO @OUUserName
END
CLOSE curOU
DEALLOCATE curOU
DROP TABLE temp_OrphanUsers
GO
But I keep getting:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 11, 2009 at 12:34 pm
If the logins are in the server the best solution is to rerestore your backup, check below for more solutions from Microsoft.
http://support.microsoft.com/kb/918992
Kind regards,
Gift Peddie
March 11, 2009 at 12:37 pm
This sounds to me like it is not able to find the login and therefore is looking for you to pass in the password as well.
you could do something generic like change your exec line to
EXEC sp_change_users_login 'Auto_Fix', @OUUserName, NULL, 'Password#1'
Which would give all orphaned users that password until your or someone else changed it. I am guessing that is your problem based on the error message anyway and it is probably worth trying out.
March 11, 2009 at 2:17 pm
Thanks.. that makes sense I guess.. as I wont know the user login information, if its not there, I'll just return a message to that effect.
Here's my code in case it helps anyone.
CREATE TABLE dbo.temp_OrphanUsers
(
UserName varchar(250) NULL,
UserSID varchar(500) NULL
) ON [PRIMARY]
INSERT INTO dbo.temp_OrphanUsers
EXEC sp_change_users_login 'Report'
DECLARE curOU CURSOR FOR
SELECT UserName FROM dbo.temp_OrphanUsers
OPEN curOU
DECLARE @OUUserName VARCHAR(250)
FETCH NEXT FROM curOU INTO @OUUserName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
BEGIN TRY
EXEC sp_change_users_login 'Auto_Fix', @OUUserName
END TRY
BEGIN CATCH
PRINT 'Unable to auto-fix ' + @OUUserName + ' - User may not have a login'
END CATCH
END
FETCH NEXT FROM curOU INTO @OUUserName
END
CLOSE curOU
DEALLOCATE curOU
DROP TABLE temp_OrphanUsers
GO
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 11, 2009 at 4:10 pm
if the server login exists and the database user exists in the database you only need to use
exec sp_change_users_login 'Update_One', 'Current user in DB', 'SQL login to link to'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2009 at 6:05 am
I've been using this script for years - never had any problems with it. Run in the database you just restored - its very similar to the other solutions above, but no need for the temp table.
-- fix_logins.sql
-- show users not mapped to a login
EXEC sp_change_users_login 'Report'
/* this script uses sp_change_users_login to tie up the database
users with the server logins - use after restoring a db
from a different server */
DECLARE @User SYSNAME
DECLARE @SQL varchar(300)
DECLARE cur_FixUser CURSOR FOR
SELECT name FROM SYSUSERS
WHERE isLogin = 1
AND isNTName = 0
AND name not in ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')
ORDER BY NAME
OPEN cur_FixUser
FETCH NEXT FROM cur_FixUser INTO @User
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL='EXEC sp_change_users_login @Action=''Update_One'', @UserNamePattern=''' +
@user + ''' , @LoginName=''' + @user + ''''
SELECT @SQL
EXEC (@SQL)
FETCH NEXT FROM cur_FixUser INTO @User
END
CLOSE cur_FixUser
DEALLOCATE cur_FixUser
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply