Add users in SQL 2000 and 2005 proc

This will add users programtically to all dbs. Change as you see fit. I added the role so that dbs can add them if they want them.

alter Procedure AddSQLUser
(@UserName varchar(200),@Pass varchar(200),@DefaultDB varchar(200),@AuthType varchar(20),
@RoleName varchar(50) = 'processadmin')

as


declare @databasename as varchar(200)

declare Curse cursor local fast_forward
for

select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb')


--check the type of authentication - and the database version -
--if we use 2000 or 2005. This will need to be updated for any new
--db version loaded.
if @authtype = 'SQL' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='8'
begin

exec ('use master')
EXEC master..sp_addlogin @userName ,@pass ,@DefaultDB
EXEC sp_grantdbaccess @UserName, @Username
EXEC sp_addsrvrolemember @UserName,@rolename

open Curse


fetch next from Curse into @databasename

while @@fetch_status = 0
begin
exec ('use ' + @databasename + '


EXEC sp_grantdbaccess ' + @UserName + ',' + @Username + '

EXEC sp_addrolemember N''db_datareader'',' + @Username + '
EXEC sp_addrolemember N''db_datawriter'',' + @Username + '
EXEC sp_addrolemember N''db_ddladmin '',' + @Username + '

')

fetch next from Curse into @databasename
end
close Curse
deallocate Curse

end


if @authtype = 'SQL' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='9'
Begin
exec ('use master')

exec ('CREATE LOGIN ' + @username + ' WITH PASSWORD='''+@pass + ''', DEFAULT_DATABASE=' + @defaultDB + ', CHECK_EXPIRATION=OFF, CHECK_POLICY=ON')
EXEC master..sp_addsrvrolemember @username, @rolename

open Curse


fetch next from Curse into @databasename

while @@fetch_status = 0
begin
exec ('
use ' + @databasename + '
CREATE USER ' + @username + ' FOR LOGIN '+ @username + '
EXEC sp_addrolemember N''db_datareader'',''' + @Username + '''
EXEC sp_addrolemember N''db_datawriter'',''' + @Username + '''
EXEC sp_addrolemember N''db_ddladmin'',''' + @Username + '''
')
fetch next from Curse into @databasename
end
close Curse
deallocate Curse

end


if @authtype = 'WINDOWS' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='8'
begin

exec ('use master')
EXEC master..sp_grantlogin @userName ,@pass ,@DefaultDB
EXEC sp_grantdbaccess @UserName, @Username
EXEC sp_addsrvrolemember @UserName,@rolename

open Curse

fetch next from Curse into @databasename

while @@fetch_status = 0
begin
exec ('use ' + @databasename + '


EXEC sp_grantdbaccess ' + @UserName + ',' + @Username + '

EXEC sp_addrolemember N''db_datareader'',' + @Username + '
EXEC sp_addrolemember N''db_datawriter'',' + @Username + '
EXEC sp_addrolemember N''db_ddladmin '',' + @Username + '

')

fetch next from Curse into @databasename
end
close Curse
deallocate Curse

end


if @authtype = 'WINDOWS' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='9'
Begin
exec ('use master')

exec ('CREATE LOGIN [ebs' + @username + '] FROM WINDOWS WITH DEFAULT_DATABASE=' + @defaultDB + '' )
exec ('exec master..sp_addsrvrolemember ''ebs'+ @username + ''','''+ @rolename + '''')

open Curse

fetch next from Curse into @databasename

while @@fetch_status = 0
begin
exec ('
use ' + @databasename + '
CREATE USER [' + @username + '] FOR LOGIN [ebs'+ @username + ']
EXEC sp_addrolemember N''db_datareader'',''' + @Username + '''
EXEC sp_addrolemember N''db_datawriter'',''' + @Username + '''
EXEC sp_addrolemember N''db_ddladmin'',''' + @Username + '''
')
fetch next from Curse into @databasename
end
close Curse
deallocate Curse

end


Michael
MCDBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7