DECLARE @DBName varchar(255)
DECLARE @MyAccount varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR
select name
from sys.databases
Where
-- Only look at databases to which we have access
has_dbaccess(name) = 1
-- Not master, tempdb or model
and name not in ('Master','tempdb','model','msdb')
order by 1
SET @MyAccount = 'MyDomain\MyLogin'
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Process Database ' + @DBName
--Mapper le user sur la base
EXEC('USE ['+@dbname+']; CREATE USER [' + @MyAccount + '] FOR LOGIN [' + @MyAccount + ']')
--Donner des droits de lecture au user sur la base
EXEC('USE ['+@dbname+'] exec sp_addrolemember "db_datareader","' + @MyAccount + '"')
--EXEC('USE ['+@dbname+'] exec sp_droprolemember "db_datareader","' + @MyAccount + '"')
--EXEC('USE ['+@dbname+']; DROP SCHEMA [' + @MyAccount + '];')
--EXEC('USE ['+@dbname+']; DROP USER [' + @MyAccount + '];')
--EXEC('USE ['+@dbname+'] exec sp_change_users_login "Update_One", "' + @MyAccount + '","' + @MyAccount + '";')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
Lien vers le fichier : cliquez ici