CODEWITHSUNDEEP
sqlsql-servercollation
Pruthvi Raj
Pruthvi Raj

Reputation: 624

SQL Server collation resolve

select 'GRANT '+permission_name+' ON ASSEMBLY::['+ sa.name +'] to
['+user_name(dp.grantee_principal_id)+'] ;' from 
sys.database_permissions dp  inner join  sys.certificates sa on 
sa.certificate_id   = dp.major_id

Upvotes: 1

Views: 84

Answers (2)

Vollmilchbb
Vollmilchbb

Reputation: 481

Maybe this will work.

select 'GRANT '+permission_name+' ON ASSEMBLY::['+ sa.name +'] to
['+user_name(dp.grantee_principal_id)+'] ;'
from sys.database_permissions dp  inner join  sys.certificates sa on 
sa.certificate_id COLLATE DATABASE_DEFAULT = dp.major_id COLLATE DATABASE_DEFAULT

Upvotes: 0

Matt
Matt

Reputation: 15061

You need to add COLLATE DATABASE_DEFAULT in the JOINS.

SELECT 'GRANT '+permission_name+' ON ASSEMBLY::['+ sa.name +'] to ['+user_name(dp.grantee_principal_id)+'] ;'
FROM sys.database_permissions dp
INNER JOIN sys.certificates sa ON sa.certificate_id COLLATE DATABASE_DEFAULT = dp.major_id COLLATE DATABASE_DEFAULT

Upvotes: 1

Related Questions