Thursday, January 17, 2008

Adding server logins to current database

Loops login names and ;
1) grants database access
2) adds db_datareader role
3) adds db_datawriter role

Includes 2 login lists, either all or logins owning sql agent jobs (uncomment as appropriate)

DECLARE @sql NVARCHAR(MAX)
DECLARE @login VARCHAR(200)
DECLARE CURSORNAME CURSOR FOR
-- all logins >

  select [name] from master.dbo.syslogins
 where [name] <> 'sa'
 and [language] is not null  

-- logins that own jobs! >

-- SELECT logins.[name]
-- FROM msdb.dbo.sysjobs jobs
-- INNER JOIN master.dbo.syslogins logins
-- ON jobs.owner_sid = logins.sid

OPEN CURSORNAME
FETCH NEXT FROM CURSORNAME INTO @login

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'CREATE USER [dummyuser] FOR LOGIN [dummyuser]
   EXEC sp_addrolemember N''db_datareader'', N''dummyuser''
   EXEC sp_addrolemember N''db_datawriter'', N''dummyuser''  '

SET @sql = REPLACE(@sql,'dummyuser',@login)

print @sql
exec sp_executesql @sql
FETCH NEXT FROM CURSORNAME INTO @login
END

CLOSE CURSORNAME
DEALLOCATE CURSORNAME

GO

No comments: