SQL Server: Post restore activities regarding login

Hi Mates,

Recently working with sql server 2005 I have faced an issue. I have refreshed the development database with the production one. and after that users can't login to the database. When I restore a database, I ame quite often just restoring users to the database and not restoring server logins. Thus, I end up in a situation that I call orphaned users: a database with users but a server with different login SIDs for those users. Then what I did was remapping the logins. The steps are like below
USE mydbname
GO
sp_change_users_login 'Report'
GO
This will show the list of the database users which need to be mapped.
USE mydbname;
GO
EXEC sp_change_users_login 'Update_One', 'user_name', 'user_name';
GO

It will then map that existing database user to a SQL Server login.

For further reading please go to the below sites

http://msdn.microsoft.com/en-us/library/ms174378.aspx

Comments

Post a Comment

Popular posts from this blog

SQL Server: Finding Query using SPID

A SPID in SQL Server is a Server Process ID. Every time an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Status of a Session: RUNNING: This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. SUSPENDED : It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue. RUNNABLE : The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the sche...
Read more

Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al...
Read more

Restoring Master, Model and MSDB from Netbackup

Restoring System Databases got two parts: Taking the instance in single user mode and Restoring the Master Database If you need to rebuild the system databases on top of this you can use the below command: "C:\Program Files\Microsoft SQL Server\ 100 \Setup Bootstrap\ SQLServer2008R2 \setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=SQL008 /SQLSYSADMINACCOUNTS=domain\ServiceUserName /SAPWD="yourpasswordOfSAuser" /SQLCOLLATION="Latin1_General_CI_AS" here 100 is for SQL Server 2008R2. It can vary case by case. How to take instance to single user mode: Two situation can happen: 1. Standalone Instance  2. Failover Cluster Instance 1. Standalone Instance: Go to SQL Config Manager SQL Service Properties -> Advanced Properties -> Startup Parameters put -m; in the Startup Parameters Apply and Okay - it will ask to restart the SQL Service to take effect. Stop and Start the service from Config Manager or you can stop and serv...
Read more