Scripts out and identifies basic database level security objects, and generates a tsql statement to recreate the objects. Here's a handy script that's part of my toolbox everywhere I go.
WINMERGE VISUAL STUDIO 2013 UPDATE
Update : Changed formatting to new style, include link to script out hashes for sql logins, other updates since 2010. On rm.grantee_principal_id = u.principal_idĪnd u.name not in ('dbo', 'sa', 'public') , TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) SELECT rm.state_desc, rm.permission_name, principal_name = QUOTENAME(u.name), u.type_desc Where r.is_disabled = 0 and m.is_disabled = 0Īnd m.name not in ('dbo', 'sa', 'public') Inner join sys.server_principals m on rm.member_principal_id = m.principal_id Inner join sys.server_principals r on rm.role_principal_id = r.principal_id , TSQL = 'EXEC master.sp_addsrvrolemember = N''' + m.name + ''', = N''' + r.name + '''' QUOTENAME(r.name) as server_role_name, r.type_desc, QUOTENAME(m.name) as principal_name, m.type_desc
WINMERGE VISUAL STUDIO 2013 WINDOWS
Select 'CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE=, DEFAULT_LANGUAGE=' I also have a database-level security script here.
I welcome to any and all feedback on these scripts. This is far from an official script, so caveat emptor. Note that this script only works on SQL 2005 or above. Scripts out and identifies basic server level security objects, and generates a tsql statement to recreate the objects.
0 kommentar(er)
