SET implicit_transactions OFFIF @@trancount > 0 ROLLBACKGODECLARE @UserName sysname = 'MYDOMAIN\%' SELECT t.p, t.obj_name FROM (SELECT 'use [' + DB_NAME() +'];' AS p, NULL AS obj_name, 1 AS sort UNION ALL --create login, returns ony if excuted in context of master SELECT 'CREATE LOGIN [' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + dbname + ']', NULL, 11 AS sort FROM sys.syslogins WHERE name LIKE ISNULL(@UserName, '%') AND db_name() = 'master' UNION ALL SELECT 'CREATE USER [' + name + '] FOR LOGIN [' + name + '];', NULL, 20 AS sort FROM sys.sysusers WHERE islogin=1 AND name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys') AND name LIKE ISNULL(@UserName, '%') UNION ALL SELECT CASE dperms.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE state_desc END + ' ' + permission_name + --cast(dperms.class as sysname) + CASE WHEN dperms.class = 0 THEN '' ELSE ' ON ' END + CASE dperms.class WHEN 0 THEN ''--'[' + DB_NAME() + ']' /*DATABASE::*/ WHEN 1 THEN CASE dperms.minor_id WHEN 0 THEN '[' + sch.[name] + '].[' + obj.[name] + ']' /*OBJECT::*/ ELSE '[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'/*OBJECT::*/ END WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'/*SCHEMA::*/ WHEN 4 THEN '[' + USER_NAME(major_id) + ']'/*USER::*/ WHEN 24 THEN '[' + symm.[name] + ']'/*SYMMETRIC KEY::*/ WHEN 25 THEN '[' + certs.[name] + ']'/*CERTIFICATE::*/ WHEN 26 THEN '[' + asymm.[name] +']'/*ASYMMETRIC KEY::*/ END + ' TO [' + dprins.[name] + ']' + CASE dperms.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;' ELSE ';' END COLLATE database_default AS 'Permissions', '[' + sch.[name] + '].[' + obj.[name] + ']', 30 AS sort FROM sys.database_permissions dperms INNER JOIN sys.database_principals dprins ON dperms.grantee_principal_id = dprins.principal_id LEFT JOIN sys.columns col ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id LEFT JOIN sys.objects obj ON dperms.major_id = obj.object_id LEFT JOIN sys.schemas sch ON obj.schema_id = sch.schema_id LEFT JOIN sys.asymmetric_keys asymm ON dperms.major_id = asymm.asymmetric_key_id LEFT JOIN sys.symmetric_keys symm ON dperms.major_id = symm.symmetric_key_id LEFT JOIN sys.certificates certs ON dperms.major_id = certs.certificate_id WHERE dperms.type <> 'CO' /* that corresponds to having the ability to connect to the database. Normally this is handled with the CREATE USER statement */ --dperms.major_id > 0 /* < 0 - would be altering permissions against system objects */ AND dprins.[name] LIKE ISNULL(@UserName, '%') --and (dperms.class = 0 /* DATABASE */ or (dperms.class > 0 and dperms.major_id > 0)) /* leave GRANT CONNECT privilege */ --and obj.is_ms_shipped=0 UNION ALL /* GROUPS */ SELECT 'exec sp_addrolemember ''' + p.NAME+ ''','+ '['+ m.NAME+']'+ ' ;', NULL, 40 AS sort FROM sys.database_role_members rm JOIN sys.database_principals p ON rm.role_principal_id = p.principal_id JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id WHERE m.name NOT LIKE 'dbo' AND m.name LIKE ISNULL(@UserName, '%') UNION ALL --system privielges, return only in context of master SELECT 'use [master]; GRANT ' + pe.permission_name + ' TO [' + pr.name + '];', NULL, 2000 AS sort FROM sys.server_principals AS pr JOIN sys.server_permissions AS pe ON pe.grantee_principal_id = pr.principal_id WHERE pr.name LIKE ISNULL(@UserName, '%') AND pr.name NOT LIKE '##%' AND pr.name NOT IN ('dbo', 'public', 'NT AUTHORITY\SYSTEM') AND pe.permission_name <> 'CONNECT SQL' AND db_name() = 'master' UNION ALL --server roles, return only in context of master SELECT 'use [master]; execute master..sp_addsrvrolemember N' + '''' + P.name + '''' + ', N' + '''' + R.name + '''' + ';', NULL, 3000 AS sort FROM sys.server_role_members RM INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R ON RM.role_principal_id = R.principal_id WHERE P.name NOT LIKE '#%' AND P.name NOT LIKE 'NT %' AND P.type_desc <> 'SERVER_ROLE' AND P.name NOT IN ('sa') AND p.name LIKE ISNULL(@UserName, '%') AND db_name() = 'master' ) AS t WHERE t.p IS NOT NULL ORDER BY sort asc, p ascGOIF @@trancount > 0 ROLLBACK