DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpexternlogin  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Omni only
6     **
7     ** Messages for "sp_helpexternlogin"
8     **
9     ** 17260, "Can't run '%1!' from within a transaction."
10    ** 17270, "There is not a server named '%1!'."
11    ** 17271, "'%1!' is the local server - remote login not applicable."
12    ** 18294, "User '%1!' is not a local user -- request denied."
13    ** 18295, "Only the 'sa' may update another's external login."
14    ** 18296, "External login updated."
15    ** 18297, "User '%1!' will be known as '%2!' in remote server '%3!'."
16    ** 18342, "Invalid name '%1!'. This role or user does not exist in this SQL Server." 
17    */
18    create procedure sp_helpexternlogin
19        @server varchar(255) = NULL, /* name of remote server */
20        @loginame varchar(255) = NULL, /* user's local name */
21        @rolename varchar(255) = NULL /* role name */
22    AS
23        BEGIN
24    
25            declare @srvid smallint
26    
27            /*
28            **  Check that the server name is valid.
29            */
30            IF @server is not null
31            BEGIN
32                select @srvid = srvid from master.dbo.sysservers
33                where srvname = @server
34    
35                if @@rowcount = 0
36                BEGIN
37                    /*		
38                    ** 17270  "There is not a server named %1!"  
39                    */
40                    raiserror 17270, @server
41                    return (1)
42                END
43    
44                /*
45                **  Check for local server.
46                */
47                if @srvid = 0
48                BEGIN
49                    /*		
50                    ** 17271 "'%1!' is the local server - remote login
51                    ** not applicable."
52                    */
53                    raiserror 17271, @server
54                    return (1)
55                END
56            END
57            ELSE
58            BEGIN
59                select @server = "%"
60            END
61    
62            /*
63            **  Check that the @loginame is valid.
64            */
65            if @loginame is not null
66            BEGIN
67                if not exists (select * from master.dbo.syslogins
68                        where name = @loginame and
69                            ((status & 512) != 512)) /* not LOGIN PROFILE */
70                BEGIN
71                    /*		
72                    ** 18294 "User '%1!' is not a local user --
73                    ** request denied."
74                    */
75                    raiserror 18294, @loginame
76                    return (1)
77                END
78            END
79            ELSE
80            BEGIN
81                select @loginame = "%"
82            END
83    
84            /*
85            **  Check that the @rolename is valid.
86            */
87            if @rolename is not null
88            BEGIN
89                if not exists (select * from master.dbo.syssrvroles
90                        where name = @rolename)
91                BEGIN
92                    /*
93                    ** 18342, "Invalid name '%1!'. This role or user does
94                    ** not exist in this SQL Server."
95                    */
96                    raiserror 18342, @rolename
97                    return (1)
98                END
99            END
100           ELSE
101           BEGIN
102               select @rolename = "%"
103           END
104   
105           /*
106           **  If only a @rolename was supplied, don't display @loginame mappings
107           */
108           if @loginame = "%" and @rolename != "%"
109               goto display_roles
110   
111           /*
112           **  Display external logins that are mapped to local login names as well as
113           **  external logins that are mapped to a NULL login name. If a specific
114           **  local login name was requested, only display its entries.
115           */
116   
117           SELECT "Server" = s.srvname,
118               "Login" = l.name,
119               "Externlogin" = a.object_cinfo
120           INTO #helpextern1rs
121           FROM master.dbo.sysattributes a,
122               master.dbo.sysservers s,
123               master.dbo.syslogins l
124           WHERE a.class = 9 AND
125               a.object_type = "EL" AND
126               a.object_info1 = s.srvid AND
127               s.srvname LIKE @server AND
128               a.object = l.suid AND
129               l.name LIKE @loginame
130           UNION
131           SELECT s.srvname,
132               NULL,
133               a.object_cinfo
134           FROM master.dbo.sysattributes a,
135               master.dbo.sysservers s
136           WHERE a.class = 9 AND
137               a.object_type = "EL" AND
138               a.object_info1 = s.srvid AND
139               s.srvname LIKE @server AND
140               a.object_info2 = - 1 AND
141               a.object = - 1 AND
142               @loginame = "%"
143           exec sp_autoformat @fulltabname = #helpextern1rs,
144               @orderby = "ORDER BY Server, Login"
145           drop table #helpextern1rs
146   
147           /*
148           ** if a specific @loginame was supplied and @rolename wasn't, don't
149           ** display @rolename mappings.
150           */
151           if @loginame != "%" and @rolename = "%"
152               goto done
153   
154   display_roles:
155   
156   
157           /*
158           **  Display external logins that are mapped to role names.
159           */
160           SELECT "Server" = s.srvname,
161               "Roles" = r.name,
162               "Externlogin" = a.object_cinfo
163           INTO #helpextern2rs
164           FROM master.dbo.sysattributes a,
165               master.dbo.sysservers s,
166               master.dbo.syssrvroles r
167           WHERE a.class = 9 AND
168               a.object_type = "EL" AND
169               a.object_info1 = s.srvid AND
170               s.srvname LIKE @server AND
171               a.object_info2 = r.srid AND
172               r.name LIKE @rolename
173           exec sp_autoformat @fulltabname = #helpextern2rs,
174               @orderby = "ORDER BY 'Server', 'Roles'"
175           drop table #helpextern2rs
176   
177   done:
178   
179           return (0)
180       END
181   
182   
183   


exec sp_procxmode 'sp_helpexternlogin', 'AnyMode'
go

Grant Execute on sp_helpexternlogin to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 143
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 173
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 126
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 136
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 167
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 169
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_helpexternlogin  
 MNER 3 No Error Check should check @@error after select into 117
 MNER 3 No Error Check should check return value of exec 143
 MNER 3 No Error Check should check @@error after select into 160
 MNER 3 No Error Check should check return value of exec 173
 MUCO 3 Useless Code Useless Begin-End Pair 23
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 179
 QGWO 3 Group by/Distinct/Union without order by 117
 QNAJ 3 Not using ANSI Inner Join 121
 QNAJ 3 Not using ANSI Inner Join 134
 QNAJ 3 Not using ANSI Inner Join 164
 QPNC 3 No column in condition 142
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type}		 124
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, object_info2, class}		 136
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type}		 167
 QUNI 3 Check Use of 'union' vs 'union all' 117
 MSUB 2 Subquery Marker 67
 MSUB 2 Subquery Marker 89
 MTR1 2 Metrics: Comments Ratio Comments: 39% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 28dec - 5exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 81 18
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, l=master..syslogins, s=master..sysservers} 0 117
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, s=master..sysservers} 0 131
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, s=master..sysservers, s2=master..syssrvroles} 0 160

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#helpextern2rs (1) 
reads table master..syslogins (1)  
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
reads table master..syssrvroles (1)  
reads table master..sysservers (1)  
writes table tempdb..#helpextern1rs (1) 
reads table master..sysattributes (1)