This script lists all users of all databases on the server.
After creating the table with all users a helper will be created only with the base to be used for source;
After a cursor so that it runs the script and execute the creation of the User, if applicable, then the User is included in the same group permission of the source will be undertaken.
This is interesting when a copy is created base
declare @login varchar(max),@base varchar(max),@permission varchar(max),@Destinationbase VARCHAR(max),@BaseSource varchar(200);
set @Destinationbase = 'AnyBase';
set @BaseSource = 'Z_Tratamento_Mailing_net'
if object_id('tempdb.dbo.#systemdbs') is not null
Drop Table #systemdbs;
CREATE TABLE #systemdbs
(
name SYSNAME
)
INSERT #systemdbs
SELECT 'master'
UNION
SELECT 'msdb'
UNION
SELECT 'model'
UNION
SELECT 'tempdb'
if object_id('tempdb.dbo.#dbusers') is not null
Drop Table #dbusers;
CREATE TABLE #dbusers
(
databasename SYSNAME,
username SYSNAME,
groupname SYSNAME,
loginname SYSNAME NULL,
defdbname SYSNAME NULL,
defschemaname SYSNAME NULL,
userid INT,
sid VARBINARY(1000)
)
if object_id('tempdb.dbo.#dbusersbuffer') is not null
Drop Table #dbusersbuffer;
CREATE TABLE #dbusersbuffer
(
username SYSNAME,
groupname SYSNAME,
loginname SYSNAME NULL,
defdbname SYSNAME NULL,
defschemaname SYSNAME NULL,
userid INT,
sid VARBINARY(1000)
)
DECLARE @Command NVARCHAR(1012)
SET @Command=
'if not exists (select * from #systemdbs where name = ''?'') begin '
+ Char(13)+
+ 'use ? ; insert #dbusersbuffer exec sp_helpuser'
+ Char(13)
+ 'insert #dbusers select ''?'', * from #dbusersbuffer'
+ Char(13) + 'truncate table #dbusersbuffer'
+ Char(13) + 'end'
--PRINT @Command
EXEC Sp_msforeachdb
@command1 = @Command
if object_id('dbo.dbusers') is not null
drop table dbusers;
select loginname ,Databasename as Base,groupName as Permission
into dbusers
from #dbusers
where Databasename = @BaseSource
declare permission cursor for
SELECT
LoginName as Login,
Base as Base,
Permission as permission
FROM dbusers
where isnull(LoginName,'SA') NOT IN ('SA',SUSER_NAME())
open permission
FETCH NEXT FROM permission INTO @login,@base,@permission
WHILE @@FETCH_STATUS = 0
begin
print 'USE '+ @Destinationbase +'
if (select count(*) from sys.sysusers where name = '''+ @login +''') = 0
begin
create user ['+@login +'] FOR LOGIN ['+ @login +'];
end
exec sp_addrolemember '''+ @permission +''','''+ @login +''';
'
FETCH NEXT FROM permission INTO @login,@base,@permission
end
close permission
deallocate permission