Shulou(Shulou.com)06/01 Report--

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-- 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.