|
- use db
- go
- DECLARE
- @Database varchar(255),
- @loginName varchar(255),
- @roleName varchar(255),
- @sql nvarchar(max);
- SET @sql=N'';
- DECLARE curLogin CURSOR LOCAL for
- select db_name() as dbname,dp.name as username,dpr.name as rolename
- from sys.database_principals dp
- join sys.database_role_members drm on drm.member_principal_id=dp.principal_id
- join sys.database_principals dpr on drm.role_principal_id=dpr.principal_id
- join sys.server_principals sp on sp.name=dp.name
- where 1=1
- --and dpr.is_fixed_role=1
- and dp.type<>'R'
- and dp.type in('S','U','G') --SQL USER,WINDOWS USER AND windows group
- order by username,rolename
- OPEN curLogin;
- FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @sql=@sql+N'
- use '+@Database+';
- if not exists(select * from '+@Database+'.sys.database_principals where name='''+@LoginName+''')
- begin
- CREATE USER '+QUOTENAME(@LoginName)+';
- end
- else
- begin
- ALTER USER '+QUOTENAME(@LoginName)+' with login = '+QUOTENAME(@LoginName)+'
- end
- ;
- '
- --print @sql
- --exec sp_executesql @sql
-
- select @sql=@sql+N'
- use '+@Database+';
- exec sp_addrolemember '''+@roleName+''', ''' + @LoginName + ''''
-
- --exec sp_executesql @sql
- FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;
- END
- CLOSE curLogin
- DEALLOCATE curLogin
- ;
- --select len(@sql)
- --print @sql --this will be truncated
- exec sysadmin.dbo.printmax @sql
- go
复制代码 |
|