Dynamic REVOKE permission_name problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic REVOKE permission_name problem

Author

Topic

ohm
Starting Member

2 Posts

Posted - 2010-06-21 : 23:23:08

Hello all! I'm trying to create a query that generates a dynamic revoke query for me, but I'm having trouble with the permission_name field from sys.database_permissions. When I include it in a concat I receive a collation error. Here's the code:

select
  'REVOKE '+p.[permission_name]+' on ['+major_link.name+'] to ['+sys.[database_principals].name+']'
from sys.database_permissions p
inner join sys.[database_principals]
  on sys.[database_principals].[principal_id] = p.grantee_principal_id
inner join sys.[database_principals] major_link
  on p.major_id = major_link.[principal_id]

The error is:

quote:
Cannot resolve collation conflict for column 1 in SELECT statement.

When I remove the '+p.[permission_name]+' from the field it runs fine, and if I select the field by itself it also runs fine...

Any ideas?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-22 : 04:10:41

[code]
select
'REVOKE ' + p.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS + ' on ['+major_link.name+'] to ['+sys.[database_principals].name+']'
from sys.database_permissions p
inner join sys.[database_principals]
on sys.[database_principals].[principal_id] = p.grantee_principal_id
inner join sys.[database_principals] major_link
on p.major_id = major_link.[principal_id]
[/code]

Or whichever collation you have/want..

ohm
Starting Member

2 Posts

Posted - 2010-06-23 : 21:41:44

That worked a treat, and I learned about the COLLATE syntax to! Thanks RickD!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources