Thanks for letting me know. I don’t do much in the way of user defined objects so I didn’t notice. I’ll certainly get it added. If you would like to email me (email is in my about page) I’ll let you know when it’s finished.

If I may I would like to report another problem I have.
I am trying to delete a db user. I am running your script and copying all the REVOKE statements first.

This is the generated script I got:

REVOKE DELETE ON [sysrowsets] FROM [myDBUser1];
REVOKE EXECUTE ON [sysrowsets] FROM [myDBUser1];
REVOKE INSERT ON [sysrowsets] FROM [myDBUser1];
REVOKE SELECT ON [sysrowsets] FROM [myDBUser1];
REVOKE UPDATE ON [sysrowsets] FROM [myDBUser1];

When I run them I get this error five times:
Msg 15151, Level 16, State 1, Line 19
Cannot find the object ‘sysrowsets’, because it does not exist or you do not have permission.

I go to SSMS and script the “revoke” by unchecking some checked check boxes I do on the GUI, this is the generated script:

use [MyDB]
GO
REVOKE DELETE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
GO
use [MyDB]
GO
REVOKE EXECUTE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
GO
use [MyDB]
GO
REVOKE INSERT ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
GO
use [MyDB]
GO
REVOKE SELECT ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
GO
use [MyDB]
GO
REVOKE UPDATE ON SCHEMA::[mySchema1] TO [myDBUser1] AS [dbo]
GO

I guess the code is not generating the right schema name “mySchema1″ and “FROM” I guess should be changed to “TO”.

Sorry I just returned to this page now. I updated for your new script and it now works for REVOKE statements.

Another bug would be when there is a schema other than the default ‘dbo’ on SPs (I presume perhaps other objects too) the GRANT and REVOKE statements you generate does not include the schema name, not even dbo.
Also the column SchemaName is NULL.

I haven’t thought this extensively but I believe the parts where you do IF @Print = 1 is wrong. Why do you need to have a different SQL string where you need to @Print or not ?
@Principal and @Role are defined as input parameters in the SP so you need their contents in the @SQL string.

In any case when I run your SP searching for a principal name, it does not work. The problem is IF @Print = 0, which is the default, the SQL will not put the values of @Principal.

Also maybe this is me not understanding but why is there a + here:
ISNULL(+QUOTENAME

I’m surprised you are having a problem. I use it on an almost daily basis.

The main difference between the strings when @Print = 1 or not is that if it’s a 0 I’m constructing a string to run in dynamic SQL. If it’s a 1 then I’m constructing a string for that I can print and someone else can run the output. So for example if @print is 1 I have to include some variable declarations that I don’t need to do if I’m not going to print out it. If @print = 0 then I will pass the parameter values in using sp_executesql and I’ll actually put them in the string.

You are right the ISNULL(+QUOTENAME is a typo although it appears to work correctly. If you would like to email me at sqlstudent at gmail dot com and maybe send me some specifics about what you are trying to do I’d love to figure it out.

Unfortunately the plugin I’m using doesn’t have a copy button like some do. Typically I click in the top left corner of the script, scroll down and hold down the shift key while clicking on the bottom right of the script. If that’s still giving you issues you can email me and I’ll email you back with a link to an easier version to copy.