Create windows login and user in all databases with dataReader and dataWriter role in SQL Server 2008

One of my recent projects was dealing in many databases in one SQL Server instance. I required one procedure which can create one Windows authenticated login and associated user in all databases.  After creating users in databases, I needed to assign datareader and datawriter roles to those users. There is not inbuilt functionality in any of the SQL Server version so thought to create one stored procedure for my need. I have used it in my project and now want to share with all of you.

CREATE PROC CreateWindowsLoginAndUser(@FullLoginName sysname, @ActionName sysname)

as

begin

declare @DataBaseName sysname

declare @SQL varchar(255)

declare @Host sysname

declare @Login sysname

set @Host = LEFT(@FullLoginName,  charindex('\',@FullLoginName)-1)

set @Login = Right(@FullLoginName,   LEN(@FullLoginName)-charindex('\',@FullLoginName))

set @SQL = ''

if (@ActionName = 'CREATE')

        set @SQL = 'USE [master] CREATE LOGIN ['+@FullLoginName+'] from windows'

else

        set @SQL = 'USE [master] DROP LOGIN ['+@FullLoginName+']'

exec (@SQL)

declare dbname cursor for

select name from sysdatabases

where name not in ('master','model','msdb','tempdb') and

DATABASEPROPERTYEX(name,'IsInStandBy') = 0 and

DATABASEPROPERTYEX(name,'Status') not in ('OFFLINE','RESTORING','RECOVERING','SUSPECT','EMERGENCY') and

DATABASEPROPERTYEX(name,'Updateability') not in ('READ_ONLY') and

DATABASEPROPERTYEX(name,'UserAccess') = 'MULTI_USER'

order by name

open dbname

fetch next from dbname into @DataBaseName

while (@@fetch_status <> -1)

begin

        if (@@fetch_status <> -2)

        begin

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] CREATE USER '+@Login+' FOR LOGIN ['+@FullLoginName+']'

        else

                set @SQL = 'USE ['+@DataBaseName+'] DROP USER '+@Login+''

        exec (@SQL)

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datareader'','''+@Login+''''

        exec (@SQL)

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datawriter'','''+@Login+''''

        exec (@SQL)

        end

fetch next from dbname into @DataBaseName

end

close dbname

deallocate dbname

end

go

Once you are done with creating script, you can execute it by following commands.

--to create user, use following TSQL

exec CreateWindowsLoginAndUser 'DomainName\WindowsLoginName','CREATE'

GO

--to drop user, use following TSQL

exec CreateWindowsLoginAndUser ' DomainName\WindowsLoginName','DROP'

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com