I am posting this as first in the series I plan to do on my favorite scripts. This is also an attempt to get into blogging seriously – an idea suggested by Tim Ford – to blog atleast one thing learnt per day(#learn365). Am starting with very mundane/basic things as there are many of them.
As a DBA I often have a need to do activities across user databases on a server. One of them is to re attach all SQL Server Logins when a Development or QA environment is restored. There are many ways of doing this…and many scripts available online. Below is the script I authored and use. I also use the same framework to perform any activity across user databases. It works on SQL Server 2005, 2008 and 2008 R2. I have not tested it on SQL Server 2012.
The script checks if login exists on the database so that it does not have to run on databases that do not have the login and produce an error. It looks really simple but when I looked for a script I could not find one that met my needs and therefore wrote my own.
/*********************************************************************************************
Authored by: Malathi Mahadevan 2/15/2013
**********************************************************************************************/
— Define variables needed
DECLARE @DataBase NVARCHAR(128)
DECLARE @Command NVARCHAR(500)
DECLARE @loginname VARCHAR(20)
–Provide login to be reattached
SELECT @loginname = ‘mylogin’
DECLARE UserDatabases CURSOR FOR
SELECT name FROM master..sysdatabases
where name not in (‘master’,’tempdb’, ‘model’, ‘msdb’, ‘distribution’)
— Open Cursor
OPEN UserDatabases
— Get First database
FETCH NEXT FROM USerDatabases INTO @DataBase
— Process until no more databases
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @command = ‘USE [‘ + @database + ‘];IF EXISTS(SELECT 1
FROM sys.database_principals princ
LEFT JOIN sys.database_permissions perm
ON perm.grantee_principal_id = princ.principal_id
where name = ”’ + @LOGINNAME + ”’and type_desc = ”SQL_USER”) ‘
+ ‘EXEC sp_change_users_login ”update_one”,’ + ”” + @LOGINNAME + ”’,’ + ”” + @LOGINNAME + ”’;’
EXEC (@command)
— Print command to be processed
PRINT @command
— Get next database
FETCH NEXT FROM UserDatabases INTO @DataBase
END
— Close and Deallocate Cursor
CLOSE UserDatabases
DEALLOCATE UserDatabases
diligentdba 