1
2 create procedure sp_aux_encr_lookup
3 @owner varchar(30),
4 @keyname varchar(255),
5 @db_name varchar(30)
6 as
7 begin
8 declare @keydbid varchar(8)
9 , @sqlstmt varchar(1100)
10
11 select @keydbid = convert(varchar(8), db_id(@db_name))
12
13 select @sqlstmt =
14 "insert #encrypted_column_info(keyname, keyowner, username, objname, colname) " +
15 "select o2.name, u.name, user_name(o1.uid), o1.name, c.name " +
16 "from syscolumns c, sysobjects o1, " +
17 @db_name + "..sysobjects o2, " + @db_name + "..sysusers u " +
18 "where c.id = o1.id and " +
19 "(c.encrkeydb = " + "'" + @db_name + "'" +
20 "or (c.encrkeydb is NULL and " + "'" + @keydbid + "'" +
21 " = convert(varchar(8), db_id()))) " +
22 "and c.encrkeyid = o2.id " +
23 "and o2.type in ('EK') " +
24 "and o2.name like " + "'" + @keyname + "' " +
25 "and o2.uid = u.uid " +
26 "and u.name like " + "'" + @owner + "'"
27
28 exec sp_exec_SQL @sqlstmt, "sp_aux_encr_lookup"
29 end
30
31
exec sp_procxmode 'sp_aux_encr_lookup', 'AnyMode'
go
Grant Execute on sp_aux_encr_lookup to public
go