Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how SQL Server derives all user rights creation statements in db". In daily operation, I believe many people have doubts about how SQL Server derives all user rights creation statements in db. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how SQL Server derives all user rights creation statements in db". Next, please follow the editor to study!

Use dbgoDECLARE @ Database varchar, @ loginName varchar, @ roleName varchar, @ sql nvarchar (max); SET @ sql=N'' DECLARE curLogin CURSOR LOCAL forselect 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_idjoin sys.database_principals dpr on drm.role_principal_id=dpr.principal_id join sys.server_principals sp on sp.name=dp.namewhere 1=1--and dpr.is_fixed_role=1 and dp.type'R'and dp.type in (SQL USER SQL USER) WINDOWS USER AND windows grouporder by username,rolenameOPEN curLogin FETCH NEXT FROM curLogin INTO @ Database,@loginName,@roleName;WHILE @ @ FETCH_STATUS = 0BEGINSET @ sql=@sql+N'use'+ @ Database+';if not exists (select * from'+ @ Database+'.sys.database_principals where name='''+@LoginName+''') beginCREATE USER'+ QUOTENAME (@ LoginName) +'; endelse beginALTER 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 @ sqlFETCH NEXT FROM curLogin INTO @ Database,@loginName,@roleName;ENDCLOSE curLoginDEALLOCATE curLogin;--select len (@ sql)-- print @ sql-- this will be truncated exec sysadmin.dbo.printmax @ sqlgo. This ends the study of "how SQL Server derives all user rights creation statements for db". I hope you can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.