Is Backup/Restore Database/Log running in SQL Server?

Sometimes it's very important to know whether database or log backup or restore is running or not!

in a word, if we need to check the stat/status of a database, we have to run below tSQL. This SQL will show the database names where a backup/restore is running whatever it is DB or LOG backup.

---------------

SELECT r.session_id ,DB_NAME(r.database_id) [Database]     ,r.command     ,CONVERT(NUMERIC(32, 2), r.percent_complete)  [Complete (%)]     ,GETDATE()  [Current Database Time]     ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) [Running Time (Minute)]     ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) [Required Time (Minute)]        ,(select t.text FROM sys.dm_exec_sql_text(sql_handle) t ) 'Statement text' FROM master.sys.dm_exec_requests r WHERE command like 'RESTORE%' or  command like 'BACKUP%'

Working with Orphan User in SQL Server

In our day to day work in SQL Server, we have to restore the database frequently. But the problem is the users which are also restored with the bak file and remain unmapped. In that case, we find the login for a user in the server level but failed to access the newly restored database.

This is very much classic problem for restore database in SQL Server. This type of unmapped users known as Orphan User.

We can map or create orphan users very easily using below tSQLs...

1. Find the Orphan Users of a database.

--Check orphaned users USE [TEST_DB] EXEC sp_change_users_login 'Report'; -- OR use below SQL USE [TEST_DB] SELECT dp.type_desc, dp.SID, dp.name AS user_name,* FROM sys.database_principals AS dp where type <> 'R' and authentication_type <> 0 and not exists (select * from sys.server_principals AS sp where  dp.SID = sp.SID)

2. If the Orphan User already exists in the server level login, then you just need to map this user to that login...

-- if login exists and need to map only -- "db_login1" is the user and login name EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

3. If the Orphan User does not exist in the server level login, then we have to create the server login using this user info ... use below SQL ...

-- if login dose not exists and need to create one EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'password'; -- OR user below Create Login command using user SID -- take User SID from below sql SELECT dp.type_desc, dp.SID, dp.name AS user_name,* FROM sys.database_principals AS dp -- Use this SID to create Login CREATE LOGIN db_login_1 WITH PASSWORD = 'use_a_strong_password_here', SID = 0xB171D3B5A352A846847342C5E46620BA; -- Use this for WINDOWS Login CREATE LOGIN db_login_1 FROM WINDOWS;

4. If you face any problem with a user in the master database, use below SQL to fix ...

-- fix master bd ALTER USER <user_name> WITH Login = <login_name>;

Which user has what access [DB Level] in SQL Server?

As a DBA or Developer, it is very important to know how much access I have or a user has?

I am working to transfer access from a server to another server. for what I just write below tSQL to find the access list of a user.

please note that this script will not show server level access. it will show database level access only.

------------
FIND all DB level access of a user: Change the login name and run this script!
------------------

DECLARE @login VARCHAR(2000) = 'DOMAIN\SAzam' CREATE TABLE #user_group (        NAME VARCHAR(2000)        ,type VARCHAR(2000)        ,privilege VARCHAR(2000)        ,map_name VARCHAR(2000)        ,original_name VARCHAR(2000)        ) INSERT INTO #user_group EXEC Xp_logininfo @login        ,'all'; DECLARE @user VARCHAR(2000) --select top 1  @user = isnull(original_name,name)  --from #USER_CROUP  --order by original_name desc  DECLARE userlist CURSOR FOR SELECT DISTINCT Isnull(original_name, NAME) FROM #user_group CREATE TABLE #userpr (        uname VARCHAR(2000)        ,dbname VARCHAR(2000)        ,rname VARCHAR(2000)        ) OPEN userlist; FETCH NEXT FROM userlist INTO @user WHILE @@FETCH_STATUS = 0 BEGIN        DECLARE @SQL VARCHAR(2000) = 'USE [?]; print db_name(); ';        SET @SQL = @SQL + 'declare @dbuser varchar(2000) = ''' + @user + '''; ' + 'SELECT @dbuser = d.name   FROM sys.database_principals AS d   INNER JOIN sys.server_principals AS s   ON d.sid = s.sid   WHERE s.name = @dbuser; '        SET @SQL = @SQL + 'insert into #UserPR SELECT u.name UName,  DB_NAME() DBName, r.name  RName FROM sys.database_role_members AS m   left JOIN sys.database_principals AS r   ON m.role_principal_id = r.principal_id   left JOIN sys.database_principals AS u   ON u.principal_id = m.member_principal_id   WHERE u.name =  @dbuser ; '        SET @SQL = @SQl + 'insert into #UserPR SELECT  @dbuser                    UName, DB_NAME() DBName, class_desc+''-''+permission_name+''-''+state_desc PName FROM sys.database_permissions WHERE grantee_principal_id = USER_ID( @dbuser ); '        EXEC Sp_msforeachdb @SQL        FETCH NEXT        FROM userlist        INTO @user END SELECT * FROM #userpr ORDER BY dbname        ,rname CLOSE userlist DEALLOCATE userlist DROP TABLE #userpr DROP TABLE #user_croup