I would not remove the GO statements that break up large batches. There are various reasons for breaking large inserts
- size (duration) of transactions
- memory requirements
- transaction log management
- mirror synchronicity
- error isolation (by batch)
- etc...
As for removing the status updates, that can be done easily. Press Ctrl-H
or from the menu, Edit -> Find and Replace -> Quick Replace
, fill it in as shown in the image below making sure to tick "Use" Regular Expressions, then click on "Replace All" to zap them all.
Have a look at sys.user_token
(http://technet.microsoft.com/en-us/library/ms188421.aspx) and sys.login_token
(http://technet.microsoft.com/en-us/library/ms186740.aspx). They tell you which roles/logins/users SQL Server is involving when figuring out the permissions for the current user.
Then you can use sys.database_permissions
(http://msdn.microsoft.com/en-us/library/ms188367.aspx) and sys.server_permissions
(http://msdn.microsoft.com/en-us/library/ms186260.aspx) to get the permissions associated with each token. (They even tell you who granted the permission.)
Finally, sys.database_principals
(http://msdn.microsoft.com/en-us/library/ms187328.aspx) and sys.server_principals
(http://msdn.microsoft.com/en-us/library/ms188786.aspx) can be used to translate the principal_id into a name.
I took a stab at combining the system views above. It might not be perfect, but it should get you started:
SELECT
T.name token_name,
T.type token_type,
T.usage,
T.is_login_token,
UDP.name user_name,
UDP.type_desc user_type_desc,
UDP.default_schema_name,
UDP.create_date,
UDP.is_fixed_role,
--UDP.authentication_type_desc,
SP.name login_name,
SP.type_desc login_type,
SP.create_date,
--SP.is_fixed_role,
DP.class_desc,
DP.permission_name,
DP.state_desc,
QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id))+'.'+QUOTENAME(O.name) object_name,
O.type_desc object_type_desc,
C.name column_name
FROM (SELECT 0 is_login_token,* FROM sys.user_token
UNION ALL
SELECT 1 is_login_token,* FROM sys.login_token
)AS T
LEFT JOIN sys.database_principals AS UDP
ON T.principal_id = UDP.principal_id
AND T.is_login_token = 0
LEFT JOIN sys.server_principals AS SP
ON (UDP.sid = SP.sid AND T.is_login_token = 0)
OR (T.principal_id = SP.principal_id AND T.is_login_token = 1)
LEFT JOIN sys.database_permissions AS DP
ON (DP.grantee_principal_id = UDP.principal_id
AND T.is_login_token = 0)
OR (DP.grantee_principal_id = SP.principal_id
AND T.is_login_token = 1)
LEFT JOIN sys.all_objects AS O
ON DP.major_id = O.object_id
LEFT JOIN sys.all_columns AS C
ON DP.major_id = C.object_id
AND DP.minor_id = C.column_id;
Best Answer
I have a couple of stored procedures that I wrote and use on a regular basis.
They each have three outputs.
Principals
: A list of the database/server principals and associated info including create/drop scripts.Role membership
: A list of what roles each database/server principal belongs to along with add/drop scripts.Permissions
: A list of individual permissions (EXECUTE, INSERT, UPDATE, CONNECT etc) for each database/server principal.Obviously the Srv sp is for the Server principals and the DB sp is for the database level principals. The DB sp also has an 'All' option for the database name. With this option it runs through and pulls all permissions for all databases.
Note: This was originally intended as a research tool so there are a fair number of options that will let you parse down your data. Just roles, everything but roles, just a specific principal name etc.