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

Tuesday, March 16, 2010 |

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

0 comments: