| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_is_valid_user | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** Messages for "sp_is_valid_user" 5 ** Currently, sp_is_valid_user does not use any messages. 6 ** 7 ** Description 8 ** 9 ** sp_is_valid_user determines if a user is a 'valid user' in a specified 10 ** database. 11 ** 12 ** For a user to be considered a 'valid user' in a database, one or more 13 ** of the following criteria must be met: 14 ** o The user has been granted sa_role. 15 ** o The user is the database owner 16 ** o The user's suid is listed in the target database's 17 ** Sysusers or Sysalternates tables. 18 ** o The target database's Sysusers table contains a 19 ** guest entry 20 ** 21 ** Parameters 22 ** @uid - (output) 23 ** Values returned are: 24 ** The user id of the user in the database. 25 ** 0 if the user is not a valid user in the database. 26 ** @dbname - name of the target database. null indicates 27 ** current database. 28 ** @loginname - name of the login being checked, null indicates 29 ** current login. 30 ** @mode - Default value is 0. This is a bit mask to special 31 ** case the search criteria. 32 ** 1 => ignore the guest user 33 ** 2 => ignore the sa_role 34 ** 4 => ignore the aliases 35 ** 36 ** Returns 37 ** 0 - sproc completed successfully 38 ** 1 - sproc didn't complete successfully as database could not be 39 ** opened. 40 ** 41 ** Note 42 ** This procedure may create a temporary table so unless the 'ddl in tran' 43 ** option is turned on for the temp database this procedure may 44 ** raise the error 2762. 45 ** 46 */ 47 create procedure sp_is_valid_user 48 @uid int output, /* Output, user id, 0 if invalid user */ 49 @dbname varchar(255) = NULL, /* db name to look in. */ 50 @loginname varchar(255) = NULL, /* login name to check for */ 51 @mode int = 0 /* Mode of search, see description */ 52 as 53 begin 54 declare @suid int, /* suid being checked for */ 55 @suidstr varchar(32), /* suid as a varchar */ 56 @cmd varchar(2000), /* Variable to store the dynamic SQL */ 57 @ignore_guest int, /* value for ignore_guest mode */ 58 @ignore_sarole int, /* value for ignore_sarole mode */ 59 @ignore_alias int /* value for ignore_alias mode */ 60 61 set nocount on 62 63 /* Initialize @uid to 0 */ 64 select @uid = 0 65 66 /* 67 ** If @loginname is not specified, work on current login. 68 ** If @dbname is not specified check in current database. 69 */ 70 select @suid = case 71 when @loginname is null then suser_id() 72 else suser_id(@loginname) 73 end 74 , @dbname = isnull(@dbname, db_name()) 75 76 /* If @suid is NULL i.e. an invalid login name is specified return. */ 77 if (@suid is NULL) 78 begin 79 return 0 80 end 81 82 /* Initialize other variables */ 83 select @suidstr = convert(varchar, @suid), 84 @ignore_guest = 1, @ignore_sarole = 2, @ignore_alias = 4 85 86 /* If we are not ignoring sa_role check if the user has sa_role. */ 87 if ((@mode & @ignore_sarole = 0) 88 and exists (select 1 from master.dbo.sysloginroles 89 where suid = @suid and srid = role_id('sa_role'))) 90 begin 91 select @uid = 1 92 return 0 93 end 94 95 /* Check if this user is the database owner. */ 96 if exists (select 1 from master.dbo.sysdatabases 97 where name = @dbname and suid = @suid) 98 begin 99 select @uid = 1 100 return 0 101 end 102 103 /* We now need to check the database so check if the db is online */ 104 if exists (select 1 from master.dbo.sysdatabases 105 where name = @dbname 106 and (status & 256 = 256 or status2 & 16 = 16)) 107 begin 108 return 1 109 end 110 111 /* Create temp table for getting uid back from dynamic SQL */ 112 create table #t(uid int) 113 114 /* Now look for the user in sysusers and sysalternates. */ 115 116 /* construct sql to query sysusers */ 117 select @cmd = "insert into #t select uid from " 118 + @dbname + ".dbo.sysusers where suid = " + @suidstr 119 120 /* If we are not ignoring aliases query sysalternates */ 121 if (@mode & @ignore_alias = 0) 122 begin 123 select @cmd = @cmd + " or suid = (select altsuid from " 124 + @dbname + ".dbo.sysalternates where suid = " 125 + @suidstr + ")" 126 end 127 128 exec (@cmd) 129 130 /* 131 ** If user exists get the uid else if we are not ignoring guest user 132 ** check for guest user, if present then return the guest user id. 133 */ 134 if (@@rowcount > 0) 135 begin 136 /* Get the uid from temp table */ 137 select @uid = uid from #t 138 end 139 else if (@mode & @ignore_guest = 0) 140 begin 141 select @cmd = "declare @dummy int select @dummy = 1 from " 142 + @dbname 143 + ".dbo.sysusers where suid = -1 and uid = 2" 144 exec (@cmd) 145 if (@@rowcount > 0) 146 begin 147 select @uid = 2 148 end 149 end 150 151 drop table #t 152 153 set nocount off 154 155 return (0) 156 end 157 158
exec sp_procxmode 'sp_is_valid_user', 'AnyMode' go Grant Execute on sp_is_valid_user to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysdatabases (1)  read_writes table tempdb..#t (1) reads table master..sysloginroles (1) CALLERS called by proc sybsystemprocs..sp_plan_dbccdb  called by proc sybsystemprocs..sp_optimal_text_space called by proc sybsystemprocs..sp_chgattribute |
